Leetcode 1907 按分类统计薪水

一、题目

查询每个工资类别的银行账户数量。 工资类别如下：

• "Low Salary"：所有工资 严格低于 20000 美元。
• "Average Salary"： 包含 范围内的所有工资 [$20000, $50000] 。
• "High Salary"：所有工资 严格大于 50000 美元。

结果表 必须 包含所有三个类别。 如果某个类别中没有帐户，则报告 0 。

按 任意顺序 返回结果表。

二、数据准备

Create table If Not Exists Accounts (account_id int, income int);
Truncate table Accounts;
insert into Accounts (account_id, income) values ('3', '108939');
insert into Accounts (account_id, income) values ('2', '12747');
insert into Accounts (account_id, income) values ('8', '87709');
insert into Accounts (account_id, income) values ('6', '91796');

三、思路

这道题难点在于存在某个类别中没有账户，如何把这一条记录反映在结果中

有两个思路：

• 基于数据表分类：从 Accounts 表入手，分别计算每个类别的账户数量，然后合并结果。
• 创建一个分类表：先定义一个类别目录表，然后通过 LEFT JOIN 来确保所有类别都在结果中。

四、方法一

-- 定义分类为 "Low Salary"
SELECT 
    'Low Salary' AS category,
    COUNT(CASE WHEN income < 20000 THEN 1 END) AS accounts_count
FROM Accounts
UNION ALL
-- 定义分类为 "Average Salary"
SELECT 
    'Average Salary',
    COUNT(CASE WHEN income BETWEEN 20000 AND 50000 THEN 1 END)
FROM Accounts
UNION ALL
-- 定义分类为 "High Salary"
SELECT 
    'High Salary',
    COUNT(CASE WHEN income > 50000 THEN 1 END)
FROM Accounts;

 五、方法二

-- 创建一个临时表，包含所有工资类别
WITH categories AS (
    SELECT 'Low Salary' AS category
    UNION ALL 
    SELECT 'Average Salary' 
    UNION ALL 
    SELECT 'High Salary'
),
 -- 创建一个临时表，通过 CASE 语句分类账户
cet AS (
    SELECT 
        CASE 
            WHEN income < 20000 THEN 'Low Salary'
            WHEN income BETWEEN 20000 AND 50000 THEN 'Average Salary'
            ELSE 'High Salary' 
        END AS category
    FROM Accounts
)
SELECT 
    c.category,
    COALESCE(COUNT(ct.category), 0) AS accounts_count
FROM categories c
LEFT JOIN cet ct ON c.category = ct.category
GROUP BY c.category

六、注意点

• UNION ALL 与 UNION 的区别
  • UNION
    • 去重：UNION 会自动去重，只返回唯一的结果行。这意味着如果多个查询返回相同的行，结果集中只会显示一次。
  • UNION ALL
    • 保留重复：UNION ALL 会保留所有的结果，包括重复的行。即使多个查询返回相同的行，结果集中也会显示每一行。
• 函数COALESCE()
  • COALESCE() 函数用于返回第一个非空值。它可以接受多个参数，并从左到右检查这些参数，直到找到一个非空值。如果所有值都是空的，则返回 NULL。
  • 在这个例子中，COALESCE() 会检查 column1，如果它是 NULL，则检查 column2，如果 column2 也是 NULL，最后返回 'default_value'。

  • SELECT COALESCE(column1, column2, 'default_value') AS result
    FROM your_table;