MySQL高阶1907-按分类统计薪水

目录

题目

准备数据

分析数据

总结

---

题目

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

按 任意顺序 返回结果表。

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

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

• "High Salary"：所有工资 严格大于 50000 美元。

准备数据

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

分析数据(方法二)

第一步:利用case函数,将工资分类

select*,casewhen income < 20000 then 'Low Salary'when income >= 20000 and income <= 50000 then 'Average Salary'when income > 50000 then 'High Salary'end category
from accounts;

第二步:再建一个t2,里边包含三个类别

select 'Low Salary' as category
union all
select 'Average Salary'
union all
select 'High Salary';

第三步:将t2左连接t1,当t1不满足t2,会产生null,此时通过coalcase函数统计个数,会将null值也统计进去.

with t1 as (select*,casewhen income < 20000 then 'Low Salary'when income >= 20000 and income <= 50000 then 'Average Salary'when income > 50000 then 'High Salary'end categoryfrom accounts
),t2 as (select 'Low Salary' as categoryunion allselect 'Average Salary'union allselect 'High Salary'
)selectt2.category,coalesce(count(account_id),0) as accounts_countfrom t2 left join t1 on t2.category = t1.category
group by t2.category;

总结

当统计不满足时输出0,可以运用左连接,再通过coalcase函数统计.