基于优先级规则统计不同状态唯一key数量的SQL查询需求

源表结构与数据 #

查询需求 #

编写SQL查询，在单行返回符合以下优先级规则的各状态对应唯一key的计数：

• a_count：所有包含status为A的唯一key数量
• b_count：包含status为B但不包含status为A的唯一key数量
• c_count：包含status为C但不包含status为A或B的唯一key数量

要求三个计数的总和等于源表中唯一key的总数，示例数据的预期结果如下：

解决方案 #

核心思路 #

先给每个唯一key分配最高优先级的状态：只要key关联过A状态就归为A类；没关联过A但关联过B就归为B类；仅关联过C的才归为C类。之后对每类的key数量做统计即可。

通用SQL实现 #

SELECT
    SUM(CASE WHEN priority_status = 'A' THEN 1 ELSE 0 END) AS a_count,
    SUM(CASE WHEN priority_status = 'B' THEN 1 ELSE 0 END) AS b_count,
    SUM(CASE WHEN priority_status = 'C' THEN 1 ELSE 0 END) AS c_count
FROM (
    SELECT
        `key`,
        CASE
            -- 先判断是否存在A状态
            WHEN EXISTS (SELECT 1 FROM your_table_name t2 WHERE t2.`key` = t1.`key` AND t2.status = 'A') THEN 'A'
            -- 再判断是否存在B状态（此时已排除有A的key）
            WHEN EXISTS (SELECT 1 FROM your_table_name t2 WHERE t2.`key` = t1.`key` AND t2.status = 'B') THEN 'B'
            -- 最后剩下的就是只有C状态的key
            ELSE 'C'
        END AS priority_status
    FROM your_table_name t1
    GROUP BY `key`
) AS key_priority_groups;

另一种高效写法（基于聚合判断） #

如果数据库支持聚合函数嵌套，也可以用更简洁的方式判断优先级：

SELECT
    SUM(has_A) AS a_count,
    SUM(has_B AND NOT has_A) AS b_count,
    SUM(has_C AND NOT has_A AND NOT has_B) AS c_count
FROM (
    SELECT
        `key`,
        MAX(CASE WHEN status = 'A' THEN 1 ELSE 0 END) AS has_A,
        MAX(CASE WHEN status = 'B' THEN 1 ELSE 0 END) AS has_B,
        MAX(CASE WHEN status = 'C' THEN 1 ELSE 0 END) AS has_C
    FROM your_table_name
    GROUP BY `key`
) AS key_status_flags;

代码说明 #

• 子查询部分：先对每个key标记是否包含A、B、C状态（或直接确定最高优先级），确保每个key只被归类一次。
• 外层查询：通过条件求和，分别统计各优先级类别的key数量，最终得到符合要求的单行结果。

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