一、按分类统计薪水
1 准备工作
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');
要求:
输入:
输出:
2 分析
3 实现
select
'Low Salary' category,
sum(income < 20000) accounts_count -- 查询薪资小于20000的账户
from Accounts
union all
select
'Average Salary' category,
sum( income >= 20000 AND income <= 50000 ) accounts_count -- 查询薪资20000-500000的账户
from Accounts
union all
select
'High Salary' category,
sum(income > 50000) accounts_count -- 查询薪资大于20000的账户
from Accounts;
-- union all 用于合并两个或多个 SELECT 语句的结果集
二、上级经理已离职的公司员工
1 准备工作
Create table If Not Exists Employees (employee_id int, name varchar(20), manager_id int, salary int);
Truncate table Employees;
insert into Employees (employee_id, name, manager_id, salary) values ('3', 'Mila', '9', '60301');
insert into Employees (employee_id, name, manager_id, salary) values ('12', 'Antonella', null, '31000');
insert into Employees (employee_id, name, manager_id, salary) values ('13', 'Emery', null, '67084');
insert into Employees (employee_id, name, manager_id, salary) values ('1', 'Kalel', '11', '21241');
insert into Employees (employee_id, name, manager_id, salary) values ('9', 'Mikaela', null, '50937');
insert into Employees (employee_id, name, manager_id, salary) values ('11', 'Joziah', '6', '28485');
要求:
输入:
输出:
2 分析
3 实现
select employee_id
from Employees
where manager_id not in (select employee_id from Employees)
and salary<30000
order by employee_id;