当前位置：首页 > news >正文

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

news 2024/9/22 0:50:05

题目

准备数据

分析数据

总结

题目

结果表必须包含所有三个类别。如果某个类别中没有帐户，则报告 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;