sql - SQL-如何在多个表之间显示重复项

我有一个包含以下两个表的数据库-Departments和OperationsDepartments的键值为:

DEPT_REF
DEPT_NAME
DEPT_FUNCTION

Operations的键值为:

OPS_REF
OPS_NAME
DEPT_REF

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

例如:
Departments

+----------+-----------+---------------+
| 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   |
+----------+-----------+---------------+

Operations

+---------+----------+----------+
| 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     |
+----------+-----------+---------------+-------+

在单个表中查找重复项没问题，但我不知道从哪里开始跨多个表进行操作，而这不得不忽略大小写。

最佳答案

如果需要区分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))，但在某些情况下可以提供重复项。