统计COL1中每个唯一值对应的COL2=0且COL3=0的出现次数

需求说明 #

要统计COL1中每个唯一值对应的COL2=0且COL3=0的记录出现次数，无论该COL1值是否存在符合条件的记录，都要在结果中显示，无符合条件记录时次数为0。

示例数据 #

期望结果 #

错误SQL及问题分析 #

你尝试的SQL语句：

SELECT DISTINCT col1, col2, col3, COUNT(*) FROM table1 WHERE col2 = 0 AND col3 = 0

存在两个核心问题：

• WHERE子句直接过滤掉了所有COL2≠0或COL3≠0的记录，导致像omega这种没有符合条件记录的COL1值根本不会出现在结果里。
• 错误使用DISTINCT和COUNT(*)组合，未通过GROUP BY进行分组统计，无法得到每个COL1值的独立计数。

正确SQL实现 #

方法1：用GROUP BY结合CASE条件计数 #

SELECT 
    col1,
    0 AS col2,
    0 AS col3,
    COUNT(CASE WHEN col2 = 0 AND col3 = 0 THEN 1 END) AS occurrence
FROM table1
GROUP BY col1
ORDER BY col1;

方法2：子查询关联所有COL1唯一值 #

SELECT 
    u.col1,
    0 AS col2,
    0 AS col3,
    COALESCE(t.count_num, 0) AS occurrence
FROM (SELECT DISTINCT col1 FROM table1) u
LEFT JOIN (
    SELECT col1, COUNT(*) AS count_num
    FROM table1
    WHERE col2 = 0 AND col3 = 0
    GROUP BY col1
) t ON u.col1 = t.col1
ORDER BY u.col1;

说明 #

• 方法1通过CASE语句仅对符合条件的记录标记有效计数，COUNT会忽略NULL值，自然得到符合条件的次数；无符合条件记录时计数自动为0。
• 方法2先获取所有COL1的唯一值集合，再左连接统计好的符合条件的计数值，用COALESCE把左连接产生的NULL转换为0，确保所有COL1值都出现在结果中。

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