按用户统计重复哈希值数量的SQL查询求助

嗨，别担心，新手入门SQL遇到分组统计的问题太正常啦～我来帮你理清思路，搞定这个按用户统计重复哈希记录数的需求。

首先明确你的核心需求：要统计每个用户名下，所有属于「重复哈希组」的记录总数——比如user1的a1出现2次，这2条都算重复；user2的b2和b3各出现2次，加起来就是4条；user3的哈希全是唯一的，所以统计结果是0。

你的原始查询问题在于：只按hash分组统计了全表的重复项，没有关联user_id，自然没法按用户拆分结果。我们可以分两步来实现目标：

步骤1：先统计每个用户每个哈希的出现次数 #

先用分组查询，得到每个用户下每个哈希的出现频次：

SELECT user_id, hash, COUNT(*) AS hash_count
FROM your_table
GROUP BY user_id, hash

执行后会得到这样的中间结果：

步骤2：汇总每个用户的重复记录总数 #

接下来，我们需要把每个用户中hash_count > 1的数值加起来，同时要确保所有用户都能显示（哪怕没有重复项）。这里用CTE（公共表达式）来实现会更清晰：

WITH user_hash_counts AS (
    -- 第一步的统计结果
    SELECT user_id, hash, COUNT(*) AS cnt
    FROM your_table
    GROUP BY user_id, hash
)
SELECT 
    all_users.user_id,
    -- 把重复组的记录数相加，没有重复的话返回0
    COALESCE(SUM(CASE WHEN uhc.cnt > 1 THEN uhc.cnt ELSE 0 END), 0) AS duplicates_hash_count
FROM (
    -- 先获取所有存在的用户，避免遗漏没有重复的用户
    SELECT DISTINCT user_id FROM your_table
) all_users
LEFT JOIN user_hash_counts uhc ON all_users.user_id = uhc.user_id
GROUP BY all_users.user_id
ORDER BY all_users.user_id;

执行这个查询后，就能得到你想要的最终结果：

补充说明 #

• COALESCE函数是为了处理那些没有任何重复哈希的用户（比如user3），确保他们的统计值显示为0而不是NULL。
• 如果你的数据库不支持CTE（比如一些老版本的MySQL），也可以把CTE换成子查询，逻辑完全一致：

SELECT 
    all_users.user_id,
    COALESCE(SUM(CASE WHEN uhc.cnt > 1 THEN uhc.cnt ELSE 0 END), 0) AS duplicates_hash_count
FROM (
    SELECT DISTINCT user_id FROM your_table
) all_users
LEFT JOIN (
    SELECT user_id, hash, COUNT(*) AS cnt
    FROM your_table
    GROUP BY user_id, hash
) uhc ON all_users.user_id = uhc.user_id
GROUP BY all_users.user_id
ORDER BY all_users.user_id;

这样应该就能完美解决你的需求啦～

备注：内容来源于stack exchange，提问作者marcoo