LeetCode mysql 刷题五:按分类统计薪水——用5种方法处理不存在的数据但要显示的数据

uccs / 203 /

ChatGPT 可用网址,仅供交流学习使用,如对您有所帮助,请收藏并推荐给需要的朋友。
https://ckai.xyz

题目

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

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

  • "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 SalaryAverage SalaryHigh 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 SalaryAverage SalaryHigh 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

  1. 使用常量查询,将 Low SalaryAverage SalaryHigh Salary 进行合并,作为 tmp1
  2. 将数据计算对应的 category 作为 tmp2,然后按照 category 进行分组,统计出每个 category 的个数
  3. 使用左连将 tmp1tmp2 进行合并,连接条件是 category,使用 ifnullnull 转换为 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 题目

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

LeetCode mysql 刷题五:按分类统计薪水——用5种方法处理不存在的数据但要显示的数据
作者
uccs
许可协议
CC BY 4.0
发布于
2023-09-25
修改于
2024-12-23
Bonnie image
尚未登录