如何在SQL统计中包含0值？薪资分类统计问题求助

问题描述 #

表：Accounts

+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id  | int  |
| income      | int  |
+-------------+------+

account_id 是该表的主键，每一行记录一个银行账户的月收入信息。

需要编写SQL统计每个薪资类别的银行账户数量，薪资类别定义：

• "Low Salary"：严格低于20000美元的薪资；
• "Average Salary"：处于[$20000, $50000]闭区间内的薪资；
• "High Salary"：严格高于50000美元的薪资。

要求结果表必须包含全部三个类别，若某类别无对应账户则返回0，结果顺序不限。

示例 #

输入Accounts表：

+------------+--------+
| account_id | income |
+------------+--------+
| 3          | 108939 |
| 2          | 12747  |
| 8          | 87709  |
| 6          | 91796  |
+------------+--------+

预期输出：

+----------------+----------------+
| category       | accounts_count |
+----------------+----------------+
| Low Salary     | 1              |
| Average Salary | 0              |
| High Salary    | 3              |
+----------------+----------------+

问题分析与修正方案 #

原代码的问题在于：当某个薪资类别没有匹配的账户时，GROUP BY 不会生成对应的行——因为原表中没有该类别的记录。要解决这个问题，需要先构造包含所有三个类别的基础数据集，再与统计结果进行左连接，确保所有类别都能被展示。

修正后的SQL代码 #

SELECT 
    c.category,
    IFNULL(a.accounts_count, 0) AS accounts_count
FROM (
    SELECT "Low Salary" AS category
    UNION ALL
    SELECT "Average Salary" AS category
    UNION ALL
    SELECT "High Salary" AS category
) c
LEFT JOIN (
    SELECT 
        CASE 
            WHEN income < 20000 THEN "Low Salary"
            WHEN income BETWEEN 20000 AND 50000 THEN "Average Salary"
            ELSE "High Salary"
        END AS category,
        COUNT(*) AS accounts_count
    FROM Accounts
    GROUP BY category
) a ON c.category = a.category;

代码说明 #

1. 构造基础类别表：通过 UNION ALL 创建一个包含三个薪资类别的临时表，确保所有需要的类别都存在。
2. 统计原表数据：从Accounts表中按类别统计账户数量，这部分逻辑和原代码一致。
3. 左连接并补0：用左连接将基础类别表和统计结果关联，再通过 IFNULL 将没有匹配结果的类别计数设为0，满足题目要求。

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