题目
题目链接:按分类统计薪水
查问每个工资类别的银行账户数量。工资类别如下:
- “Low Salary”:所有工资 严格低于 20000 美元。
- “Average Salary”:蕴含 范畴内的所有工资 [$20000, $50000]。
- “High Salary”:所有工资 严格大于 50000 美元。
后果表 必须 蕴含所有三个类别。如果某个类别中没有帐户,则报告 0。
按 任意程序 返回后果表。
查问后果格局如下示例。
输出:Accounts 表:
+------------+--------+
| account_id | income |
+------------+--------+
| 3 | 108939 |
| 2 | 12747 |
| 8 | 87709 |
| 6 | 91796 |
+------------+--------+
在 SQL 中,account_id 是这个表的主键。每一行都蕴含一个银行帐户的月支出的信息。输入:+----------------+----------------+
| category | accounts_count |
+----------------+----------------+
| Low Salary | 1 |
| Average Salary | 0 |
| High Salary | 3 |
+----------------+----------------+
解释:低薪: 有一个账户 2.
中等薪水: 没有.
高薪: 有三个账户,他们是 3, 6 和 8.
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');
解析
因为表中没有 Average Salary
类型的数据,然而后果须要将 Average Salary
类型的数据用 0
示意
所以本题考查的点是:如何给不存在的账户类型赋值为 0
。
解决这个问题须要应用 常量查问,而后应用 union all
将后果合并。
如何统计出 null
呢?次要有四种办法:
count(*)
sum(if(expr, 1, 0))
/sum(ifnull(expr, 0))
sum(case when expr then 1 else 0 end)
- 左连
办法一
办法一是应用 count(*)
可能统计出 Low Salary
、Average Salary
、High Salary
的个数,而后应用 union all
将后果合并。
SELECT "Low Salary" category, COUNT(*) accounts_count FROM Accounts WHERE income < 20000
UNION ALL
SELECT "Average Salary" category, COUNT(*) accounts_count FROM Accounts WHERE income >= 20000 AND income <= 50000
UNION ALL
SELECT "High Salary" category, COUNT(*) accounts_count FROM Accounts WHERE income > 50000
ORDER BY accounts_count DESC
办法二
办法二是应用 sum(if(expr, 1, 0))
统计出 Low Salary
、Average Salary
、High Salary
的个数,而后应用 union all
将后果合并。
这里的 if(expr, 1, 0)
能够替换成 ifnull(expr, 0)
或者 case when expr then 1 else 0 end
。
SELECT "Low Salary" category, SUM(IF(income < 20000, 1, 0)) accounts_count FROM Accounts
UNION ALL
SELECT "Average Salary" category, SUM(IF(income >= 20000 AND income <= 50000, 1, 0)) accounts_count FROM Accounts
UNION ALL
SELECT "High Salary" category, SUM(IF(income > 50000, 1, 0)) accounts_count FROM Accounts
ORDER BY accounts_count DESC
办法三
办法三应用左连,将 Average Salary
类型的数据置为 0
- 应用常量查问,将
Low Salary
、Average Salary
、High Salary
进行合并,作为tmp1
表 - 将数据计算对应的
category
作为tmp2
,而后依照category
进行分组,统计出每个category
的个数 - 应用左连将
tmp1
和tmp2
进行合并,连贯条件是category
,应用ifnull
将null
转换为0
WITH tmp1 AS (
SELECT "Low Salary" category
UNION ALL
SELECT "Average Salary"
UNION ALL
SELECT "High Salary"
)
SELECT
tmp1.category, ifnull(tmp2.n, 0) accounts_count
FROM
tmp1
LEFT JOIN (
SELECT
CASE
WHEN income < 20000 THEN "Low Salary"
WHEN (income >= 20000 AND income <= 50000) THEN "Average Salary" ELSE "High Salary"
END category,
count(1) AS n
FROM
Accounts
GROUP BY category
) tmp2 USING (category)
ORDER BY accounts_count DESC
往期 MySQL 题目
- MySQL 题目
- LeetCode mysql 刷题一:计算非凡奖金 | 买下所有产品的客户
- LeetCode mysql 刷题二:电影评分——判断日期的五种办法
- LeetCode mysql 刷题三:确认率——MySQL 中的 null 解决 | 判断三角形的四种办法
- LeetCode mysql 刷题四:餐馆营业额变动增长——用自连和窗口函数 4 种 sql 实现过来 7 天的营业额