MySQL基础面试题——高级查询、链接

一、按分类统计薪水

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;