SQL - 如何显示多个表中的重复项

Question

我有一个数据库，其中包含以下两个表 - Departments 和 Operations

Departments 的键值为:

DEPT_REF
DEPT_NAME
DEPT_FUNCTION

Operations 的关键值是:

OPS_REF
OPS_NAME
DEPT_REF

我需要解决的是一个查询，该查询将为我提供 Operations.OPS_NAME、Departments.DEPT_NAME 和 Departments.DEPT_FUNCTION 的列表> 它们是重复的，但应该忽略大小写。理想情况下，它也应该有计数。

例如:

部门

+----------+-----------+---------------+
| DEPT_REF | DEPT_NAME | DEPT_FUNCTION |
+----------+-----------+---------------+
| 0001     | Home      | Live here     |
+----------+-----------+---------------+
| 0002     | HOME      | LIVE HERE     |
+----------+-----------+---------------+
| 0003     | HOME      | Live here     |
+----------+-----------+---------------+
| 0004     | work      | Work Here     |
+----------+-----------+---------------+
| 0005     | Work      | Work Here     |
+----------+-----------+---------------+
| 0006     | OTHER     | Other Stuff   |
+----------+-----------+---------------+

操作

+---------+----------+----------+
| OPS_REF | OPS_NAME | DEPT_REF |
+---------+----------+----------+
| 000A    | OPS1     | 0001     |
+---------+----------+----------+
| 000B    | Ops1     | 0001     |
+---------+----------+----------+
| 000C    | ops1     | 0002     |
+---------+----------+----------+
| 000D    | OPS2     | 0003     |
+---------+----------+----------+
| 000E    | ops2     | 0001     |
+---------+----------+----------+
| 000F    | ops2     | 0004     |
+---------+----------+----------+
| 000G    | OPS3     | 0004     |
+---------+----------+----------+
| 000H    | OPS3     | 0005     |
+---------+----------+----------+

我正在寻找的输出将是一个以大写字母显示并带有计数的操作名称、部门名称和部门职能的列表。因此，从我希望看到的示例数据来看:

+----------+-----------+---------------+-------+
| OPS_NAME | DEPT_NAME | DEPT_FUNCTION | COUNT |
+----------+-----------+---------------+-------+
| OPS1     | HOME      | LIVE HERE     | 3     |
+----------+-----------+---------------+-------+
| OPS2     | HOME      | LIVE HERE     | 2     |
+----------+-----------+---------------+-------+

在单个表中查找重复项没有问题，但我不知道从哪里开始跨多个表执行此操作，必须忽略大小写。

Answer 1

如果您需要分隔 OPS_NAME 的大小写，您可以使用将它们转换为二进制数据类型:

SELECT  UPPER(o.OPS_NAME) as OPS_NAME,
        UPPER(d.DEPT_NAME) as DEPT_NAME,
        UPPER(d.DEPT_FUNCTION) as DEPT_FUNCTION,
        COUNT(DISTINCT CAST(o.OPS_NAME As BINARY)) AS [COUNT]
FROM Departments d
INNER JOIN Operations o
    ON o.DEPT_REF = d.DEPT_REF
GROUP BY o.OPS_NAME,
        d.DEPT_NAME,
        d.DEPT_FUNCTION
HAVING COUNT(DISTINCT CAST(o.OPS_NAME As BINARY)) > 1

输出:

OPS_NAME    DEPT_NAME   DEPT_FUNCTION   COUNT
OPS1        HOME        LIVE HERE       3
OPS2        HOME        LIVE HERE       2

编辑

您还可以使用 COUNT(DISTINCT o.OPS_NAME COLLATE Latin1_General_CS_AS) 正如评论中所建议的那样。在您的情况下，COUNT(DISTINCT BINARY_CHECKSUM(o.OPS_NAME)) 也可以工作，但在某些情况下它可以提供重复项。