LeetCode 1907.按分类统计薪水

数据准备

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');

需求

/*
 写出一个SQL查询，来报告每个工资类别的银行账户数量。工资类别如下：
"Low Salary"：所有工资 严格低于 20000 美元。
"Average Salary"： 包含 范围内的所有工资[$20000,$50000] 。
"High Salary"：所有工资 严格大于 50000 美元。
结果表 必须 包含所有三个类别。如果某个类别中没有帐户，则报告0 。
 */

输入

输出

方法一:

with t1 as (
    select *,
       case
           when income<20000 then 'Low Salary'
           when income>50000 then 'High Salary'
           else 'Average Salary'
       end as rn1
    from Accounts
)
select rn1 as category,
       count(1) as 'accounts_count'
from t1
group by rn1
;

方法二:

with t1 as (
    select 'Low Salary' as category ,
       count(1) as accounts_count
    from Accounts
    where income<20000
),t2 as (
    select 'Average Salary' as category,
       coalesce(count(1),0) as accounts_count
    from Accounts
    where income>= 20000 and income<=50000
),t3 as (
    select 'High Salary' as category,
       coalesce(count(1),0) as accounts_count
    from Accounts
    where income>50000
)
select * from t1
union all
select * from t2
union all
select * from t3
;