我有一个包含以下两个表的数据库-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它们是重复项，但应忽略大小写。理想情况下，它也应该计数。
例如：
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可以使用CAST将它们转换为二进制数据类型：
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))但是在某些情况下可以提供重复项。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句