【SQL】1907. 按分类统计薪水（IF语句；CASE 语句；UNION）

前述

知识点回顾：

1. case when then else end
2. Mysql：条件判断函数-CASE WHEN、IF、IFNULL详解

题目描述

leetcode 题目：1907. 按分类统计薪水

1. 写法一：if 语句

select 
    'Low Salary' as category,
    sum(if(income < 20000, 1, 0)) as accounts_count 
from Accounts
union
select
    'Average Salary' as category,
    sum(if(income >= 20000 and income <= 50000, 1, 0)) as accounts_count
from Accounts
union
select
    'High Salary' as category,
    sum(if(income > 50000, 1, 0)) as accounts_count
from Accounts

2. 写法二：case when then else end

select 
    'Low Salary' as category,
    sum(case when income < 20000 then 1 else 0 end) as accounts_count 
from Accounts
union
select
    'Average Salary' as category,
    sum(case when income >= 20000 and income <= 50000 then 1 else 0 end) as accounts_count
from Accounts
union
select
    'High Salary' as category,
    sum(case when income > 50000 then 1 else 0 end) as accounts_count
from Accounts

3. 记录自己的错误：

-- ERROR!!!
-- select
--     case 
--         when income < 20000 then 'Low Salary'
--         when income > 50000 then 'High Salary'
--         else 'Average Salary'
--     end as category,
--     count(*) as accounts_count 
-- from Accounts
-- group by category;

错误原因：