题目

题目链接：按分类统计薪水

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

  • "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 呢？次要有四种办法：

  1. count(*)
  2. sum(if(expr, 1, 0)) / sum(ifnull(expr, 0))
  3. sum(case when expr then 1 else 0 end)
  4. 左连

办法一

办法一是应用 count(*) 可能统计出 Low Salary、Average Salary、High Salary 的个数，而后应用 union all 将后果合并。

SELECT "Low Salary" category, COUNT(*) accounts_count FROM Accounts WHERE income < 20000UNION ALLSELECT "Average Salary" category, COUNT(*) accounts_count FROM Accounts WHERE income >= 20000 AND income <= 50000UNION ALLSELECT "High Salary" category, COUNT(*) accounts_count FROM Accounts WHERE income > 50000ORDER 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 AccountsUNION ALLSELECT "Average Salary" category, SUM(IF(income >= 20000 AND income <= 50000, 1, 0)) accounts_count FROM AccountsUNION ALLSELECT "High Salary" category, SUM(IF(income > 50000, 1, 0)) accounts_count FROM AccountsORDER BY accounts_count DESC

办法三

办法三应用左连，将 Average Salary 类型的数据置为 0

  1. 应用常量查问，将 Low Salary、Average Salary、High Salary 进行合并，作为 tmp1 表
  2. 将数据计算对应的 category 作为 tmp2，而后依照 category 进行分组，统计出每个 category 的个数
  3. 应用左连将 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_countFROM  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 题目

  1. MySQL 题目
  2. LeetCode mysql 刷题一：计算非凡奖金 | 买下所有产品的客户
  3. LeetCode mysql 刷题二：电影评分——判断日期的五种办法
  4. LeetCode mysql 刷题三：确认率——MySQL 中的 null 解决 | 判断三角形的四种办法
  5. LeetCode mysql 刷题四：餐馆营业额变动增长——用自连和窗口函数 4 种 sql 实现过来 7 天的营业额