1907. Count Salary Categories

最新推荐文章于 2025-05-21 18:24:26 发布

Smile sea breeze

最新推荐文章于 2025-05-21 18:24:26 发布

阅读量698

点赞数 20

分类专栏： Leetcode-sql 50道文章标签：数据库

本文链接：https://blog.csdn.net/weixin_45428910/article/details/141338917

版权

Leetcode-sql 50道专栏收录该内容

69 篇文章

订阅专栏

SQL 50道

内容持续更新…（始于2024年08月03日）

1.问题

Table: Accounts

±------------±-----+
| Column Name | Type |
±------------±-----+
| account_id | int |
| income | int |
±------------±-----+
account_id is the primary key (column with unique values) for this table.
Each row contains information about the monthly income for one bank account.

Write a solution to calculate the number of bank accounts for each salary category. The salary categories are:

“Low Salary”: All the salaries strictly less than $20000.
“Average Salary”: All the salaries in the inclusive range [$20000, $50000].
“High Salary”: All the salaries strictly greater than $50000.
The result table must contain all three categories. If there are no accounts in a category, return 0.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input:
Accounts table:
±-----------±-------+
| account_id | income |
±-----------±-------+
| 3 | 108939 |
| 2 | 12747 |
| 8 | 87709 |
| 6 | 91796 |
±-----------±-------+
Output:
±---------------±---------------+
| category | accounts_count |
±---------------±---------------+
| Low Salary | 1 |
| Average Salary | 0 |
| High Salary | 3 |
±---------------±---------------+
Explanation:
Low Salary: Account 2.
Average Salary: No accounts.
High Salary: Accounts 3, 6, and 8.

2. 解题思路

方法1：

SUM(CASE WHEN … THEN 1 ELSE 0 END):

这里使用 CASE 语句根据不同的收入范围分类。
SUM 函数对 CASE 语句的结果进行求和，计算每个分类中的账户数量。CASE 语句会返回 1 或 0，根据条件是否满足。

UNION ALL:

将三个查询结果合并成一个结果集。UNION ALL 用于包含所有记录，不去除重复行（这里没有重复行问题）。

每个查询:

‘Low Salary’: 计算收入少于 20000 的账户数量。
‘Average Salary’: 计算收入在 20000 到 50000 之间（包含）的账户数量。
‘High Salary’: 计算收入高于 50000 的账户数量。

3. 代码

代码1：

SELECT 
    'Low Salary' AS category,
    SUM(CASE WHEN income < 20000 THEN 1 ELSE 0 END) AS accounts_count
FROM Accounts

UNION ALL

SELECT 
    'Average Salary' AS category,
    SUM(CASE WHEN income BETWEEN 20000 AND 50000 THEN 1 ELSE 0 END) AS accounts_count
FROM Accounts

UNION ALL

SELECT 
    'High Salary' AS category,
    SUM(CASE WHEN income > 50000 THEN 1 ELSE 0 END) AS accounts_count
FROM Accounts;

select 'High Salary' as category, (select count(*) from Accounts where income > 50000) as accounts_count
union all
select 'Average Salary' as category, (select count(*) from Accounts where income >= 20000 and income <= 50000) as accounts_count
union all
select 'Low Salary' as category, (select count(*) from Accounts where income < 20000) as accounts_count