统计重复ID对应不同姓名出现次数的SQL实现问题

原始表结构与数据 #

需求说明 #

需要统计存在重复ID的行中，每个ID对应的不同姓名的出现次数，期望结果如下（建议给列名加上区分标识，避免重复的count列名）：

说明：ID为001的行中，Johnson出现2次，John出现1次；ID为458的行中，Katherine和Lucy各出现1次

现有查询的问题 #

你之前写的查询：

select id, name, count(id), count(name) from Students group by id, name having count(id)>1 and count(name)>1

只筛选出了出现次数大于1的姓名分组，漏掉了每个ID下出现次数为1的姓名，且分组逻辑无法将同一ID的所有姓名统计结果合并到一行。

正确解决方案 #

方案一：固定姓名统计（适用于已知姓名范围） #

如果明确要统计的姓名是固定值，用条件聚合实现：

SELECT 
    id,
    COUNT(CASE WHEN Name = 'Johnson' THEN 1 END) AS johnson_count,
    COUNT(CASE WHEN Name = 'John' THEN 1 END) AS john_count,
    COUNT(CASE WHEN Name = 'Katherine' THEN 1 END) AS katherine_count,
    COUNT(CASE WHEN Name = 'Lucy' THEN 1 END) AS lucy_count
FROM Students
WHERE id IN (SELECT id FROM Students GROUP BY id HAVING COUNT(id) > 1)
GROUP BY id

该查询先筛选出有重复ID的记录，再对每个ID下的不同姓名做条件计数，结果将每个姓名的次数作为独立列展示，清晰直观。

方案二：行式统计（适用于姓名不固定场景） #

如果姓名不确定，想以行的形式展示每个ID下的姓名及其出现次数：

SELECT 
    s.id,
    s.Name,
    COUNT(*) AS name_count
FROM Students s
INNER JOIN (
    SELECT id FROM Students GROUP BY id HAVING COUNT(id) > 1
) dup_ids ON s.id = dup_ids.id
GROUP BY s.id, s.Name
ORDER BY s.id, s.Name

查询结果：

方案三：合并为单行展示（方言特定） #

如果需要将同一ID的多个姓名次数合并到一行，不同SQL方言有不同实现：

MySQL版本：

SELECT 
    id,
    GROUP_CONCAT(CONCAT(Name, ': ', COUNT(*)) SEPARATOR ', ') AS name_counts
FROM Students s
INNER JOIN (
    SELECT id FROM Students GROUP BY id HAVING COUNT(id) > 1
) dup_ids ON s.id = dup_ids.id
GROUP BY s.id

结果：

SQL Server版本：

SELECT 
    id,
    STRING_AGG(CONCAT(Name, ': ', cnt), ', ') AS name_counts
FROM (
    SELECT 
        s.id,
        s.Name,
        COUNT(*) AS cnt
    FROM Students s
    INNER JOIN (
        SELECT id FROM Students GROUP BY id HAVING COUNT(id) > 1
    ) dup_ids ON s.id = dup_ids.id
    GROUP BY s.id, s.Name
) t
GROUP BY id

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