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
    *,
    case
        when 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
        *,
        case
            when 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 as (
    select 'Low Salary' as category
    union all
    select 'Average Salary'
    union all
    select 'High Salary'
)select
     t2.category,
     coalesce(count(account_id),0) as accounts_count
 from t2 left join t1 on t2.category = t1.category
group by t2.category;

总结

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

原文地址：https://blog.csdn.net/weixin_58305115/article/details/142426360

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！