SQL查询需求：获取存在多姓氏的重复组及各组合出现次数

嘿，我明白你的困扰了！要搞定这个需求，我们可以分两步走：先找出那些同一GroupNo下存在多种不同LastName的组，再统计这些组里每个GroupNo/LastName组合的出现次数。下面给你两种实用的实现方案，还附带示例说明~

方案一：子查询关联法 #

这是最直观的写法，先筛选符合条件的组，再关联原表统计次数：

SELECT 
    t.GroupNo,
    t.LastName,
    COUNT(*) AS OccurrenceCount
FROM 
    your_table_name t
INNER JOIN (
    -- 第一步：找出有多种不同姓氏的GroupNo
    SELECT GroupNo
    FROM your_table_name
    GROUP BY GroupNo
    HAVING COUNT(DISTINCT LastName) > 1
) filtered_groups ON t.GroupNo = filtered_groups.GroupNo
GROUP BY 
    t.GroupNo,
    t.LastName
ORDER BY 
    t.GroupNo,
    OccurrenceCount DESC;

代码解释： #

• 内层子查询：通过GROUP BY GroupNo分组，用COUNT(DISTINCT LastName)统计每个组的不同姓氏数量，只保留数量大于1的组（也就是存在多种姓氏的组）。
• 外层查询：将原表和筛选出的组关联，再按GroupNo和LastName分组，用COUNT(*)统计每个组合的出现次数，最后按组和次数排序，方便查看。

方案二：窗口函数法 #

如果你习惯用窗口函数，这种写法不需要关联子查询，逻辑同样清晰：

SELECT 
    GroupNo,
    LastName,
    OccurrenceCount
FROM (
    SELECT 
        GroupNo,
        LastName,
        -- 统计当前GroupNo+LastName组合的出现次数
        COUNT(*) OVER (PARTITION BY GroupNo, LastName) AS OccurrenceCount,
        -- 统计当前GroupNo下的不同姓氏数量
        COUNT(DISTINCT LastName) OVER (PARTITION BY GroupNo) AS DistinctLastNameCount
    FROM your_table_name
) sub_query
WHERE DistinctLastNameCount > 1
ORDER BY GroupNo, OccurrenceCount DESC;

代码解释： #

• 内层子查询用两个窗口函数：
  • COUNT(*) OVER (PARTITION BY GroupNo, LastName)：给每条记录标记它所在的GroupNo+LastName组合的总次数。
  • COUNT(DISTINCT LastName) OVER (PARTITION BY GroupNo)：给每条记录标记它所在GroupNo的不同姓氏总数。
• 外层查询只保留DistinctLastNameCount > 1的记录，也就是来自多姓氏组的组合，最终得到你需要的结果。

示例演示 #

假设你的表数据是这样的：

运行上面的查询后，你会得到这样的结果：

可以看到，只有G1和G3（存在多种姓氏的组）被保留，每个姓氏的出现次数也清晰统计出来了~

注意事项 #

• 记得把代码里的your_table_name替换成你实际使用的表名。
• 如果你的LastName字段有NULL值，COUNT(DISTINCT LastName)会自动忽略NULL。如果需要把NULL当作一种特殊的“姓氏”统计，可以改成COUNT(DISTINCT COALESCE(LastName, 'NULL'))。

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