SQL按角色分组统计多列唯一组合，筛选计数>1结果异常问题

需求与现状 #

需求：按cast_roles分组，统计cast_characters与cast_identities的唯一组合中出现次数>1的数量，最终保留每个角色对应的有效组合数。

现有数据查询语句：

SELECT *
FROM cast
ORDER BY cast_characters, cast_identities, cast_roles

对应数据：

错误执行的SQL：

SELECT cast_roles, COUNT(DISTINCT CONCAT(cast_characters, cast_identities, cast_roles)) AS 'cnt'
FROM cast
GROUP BY cast_roles
HAVING cnt > 1;

期望结果：

实际得到的结果：

错误原因 #

1. 统计逻辑偏差：COUNT(DISTINCT CONCAT(...))仅统计当前角色下cast_characters+cast_identities+cast_roles的唯一组合总数，没有过滤掉单个组合出现次数≤1的情况。比如Villain中的Alice/Susan组合仅出现1次，但仍被算作一个唯一组合，导致Villain的cnt被统计为2。
2. HAVING子句不符合需求：原SQL的HAVING cnt >1是过滤掉有效组合数≤1的角色，但用户实际需要保留所有有有效组合的角色（哪怕数量为1），同时原条件因为Villain的cnt实际为2而被保留，进一步偏离需求。

正确SQL实现 #

需要分两步处理：先筛选出出现次数>1的(cast_roles, cast_characters, cast_identities)组合，再按角色统计这些有效组合的数量。

方法1：使用CTE（通用表表达式） #

WITH valid_combinations AS (
    SELECT cast_roles, cast_characters, cast_identities
    FROM cast
    GROUP BY cast_roles, cast_characters, cast_identities
    HAVING COUNT(*) > 1
)
SELECT cast_roles, COUNT(*) AS cnt
FROM valid_combinations
GROUP BY cast_roles;

方法2：使用子查询 #

SELECT cast_roles, COUNT(*) AS cnt
FROM (
    SELECT cast_roles, cast_characters, cast_identities
    FROM cast
    GROUP BY cast_roles, cast_characters, cast_identities
    HAVING COUNT(*) > 1
) AS valid_groups
GROUP BY cast_roles;

结果验证 #

执行上述SQL后，将得到符合需求的结果：

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