按id、name分组后统计department/email列差异记录数的SQL实现

需求说明 #

现有数据表tb：

id name department email
1  Alex IT         blah@gmail.com
1  Alex IT         blah@gmail.com
2  Jay  HR         jay@gmail.com
2  Jay  Marketing  zou@gmail.com

需按id、name分组，统计组内department和email列存在差异的记录数（完全重复的组统计为0，有n个不同(department,email)组合的组统计为n-1），期望结果：

id name count(*)
1  Alex 0
2  Jay  1

实现SQL #

支持多列DISTINCT的数据库（如MySQL 8.0+、PostgreSQL）

SELECT 
  id, 
  name, 
  COUNT(DISTINCT department, email) - 1 AS `count(*)`
FROM tb
GROUP BY id, name;

不支持多列DISTINCT的数据库（如老版本MySQL）

使用CONCAT_WS拼接列（避免NULL值导致的统计错误）：

SELECT 
  id, 
  name, 
  COUNT(DISTINCT CONCAT_WS(',', department, email)) - 1 AS `count(*)`
FROM tb
GROUP BY id, name;

逻辑说明 #

• COUNT(DISTINCT department, email)（或拼接后的列）用于统计每个分组内，唯一的(department,email)组合数量。
• 减去1是因为：若分组内所有记录的(department,email)完全一致，唯一组合数为1，减1后得到0；若有n个不同组合，减1后即为差异的组数，符合需求。

内容的提问来源于stack exchange，提问作者moth