SQL面试统计函数、LIMIT OFFSET、left join

一、目录

题目链接：
https://www.nowcoder.com/ta/sql

题目	知识点
11	join连接、<>不相等用法
12	join连接、查找最值、group by
13	group by、count()、统计大于某值用法
14	分组、统计不重复值个数
15	查找奇偶数、排序
16	group by分组、条件限制下的查找平均值
17	查找数值第二大、LIMIT x OFFSET y
18	不使用order进行排序、not in 用法
19	三表联合、inner join 与 left join区别
20	生成新差值

11. 获取所有员工当前的manager

• 题目描述

获取所有员工当前的(dept_manager.to_date='9999-01-01')manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_no。
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL, -- '所有的员工编号'
`dept_no` char(4) NOT NULL, -- '部门编号'
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部门编号'
`emp_no` int(11) NOT NULL, -- '经理编号'
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

• 思路
  1.通过emp_no链接两个表；
  2.限定条件：dept_manager.to_date=‘9999-01-01’ ,

• 代码

SELECT de.emp_no, dm.emp_no AS manager_no
FROM dept_emp AS de INNER JOIN dept_manager AS dm ON dm.dept_no = de.dept_no
WHERE dm.to_date = '9999-01-01'  AND de.emp_no <> dm.emp_no

12. 获取所有部门中当前员工薪水最高的相关信息

• 题目描述

获取所有部门中当前(dept_emp.to_date = '9999-01-01')员工当前(salaries.to_date='9999-01-01')薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门升序排列。
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

• 思路
  1.通过emp_no 用inner join 连接两表
  2.按照部门dept_no分组
  3.薪水最高取MAX（salary）；
• 代码

select de.dept_no,de.emp_no,max(s.salary) from 
dept_emp de join salaries s on de.emp_no=s.emp_no
WHERE de.to_date = '9999-01-01' and s.to_date = '9999-01-01'
GROUP BY de.dept_no

13. 从titles表获取按照title进行分组

• 题目描述

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
CREATE TABLE IF NOT EXISTS “titles” (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

• 思路
  1.title分组
  2.在每个分组下，用count()统计emp_no数目

• 代码

select title,count(emp_no) as t 
from titles 
group by title
HAVING t>=2

14. 从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略

• 题目描述

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。
CREATE TABLE IF NOT EXISTS “titles” (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

• 思路
  1.title分组
  2.在每个分组下，用count()统计emp_no数目
  3.distinct去重emp_no

• 代码

SELECT title, COUNT(DISTINCT emp_no) AS t
FROM titles
GROUP BY title
HAVING t >= 2

15. 查找employees表

• 题目描述

查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

• 思路
  1.使用 % 限定条件emp_no为奇数
  2.last_name <> ‘Mary’
  3.order by 逆序排序

• 代码

select * from employees
where last_name <> 'Mary' and emp_no % 2 == 1
order by hire_date desc

16. 统计出当前各个title类型对应的员工当前薪水对应的平均工资

• 题目描述

统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
CREATE TABLE IF NOT EXISTS “titles” (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

• 思路
  1.通过emp_no链接两个表，select对象avg()
  2.限定条件s.to_date=‘9999-01-01’，t.to_date=‘9999-01-01’
  3.按照title类型统计，用GROUP BY分组；

• 代码

select title,avg(salary) as avg
from salaries s join titles t on s.emp_no=t.emp_no
where s.to_date='9999-01-01' and t.to_date='9999-01-01'
group by title

• 参考
  1.“Where” 是一个约束声明，使用Where来约束来之数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。
  2.“Having”是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。

17. 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

• 题目描述

获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

• 思路
  1.第一小的salary的最大值就是第二大的salary员工信息；
  2.只取出薪水第二多这一条记录，ORDER BY排序后，用 LIMIT 和 OFFSET限制。

• 代码
  方法一

SELECT emp_no,max(salary)
from salaries
where salary<(select max(salary) from salaries)

方法二

SELECT emp_no, salary
FROM salaries
WHERE to_date = '9999-01-01' AND salary = 
(SELECT DISTINCT salary FROM salaries WHERE to_date = '9999-01-01'
ORDER BY salary DESC LIMIT 1 OFFSET 1)

• 参考
  LIMIT 和 OFFSET的用法
  https://blog.youkuaiyun.com/chen1042246612/article/details/84109613
  LIMIT 3 OFFSET 1， 这意味着，跳过第1条记录（即从第2条记录开始），返回接下来3条记录。即最终得到，原本的第2，3，4条记录。

18. 从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略

• 题目描述

查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

• 思路
  1.排除第一大salary记录
  2.选取余下记录的salary的最大值

• 代码

select e.emp_no,max(s.salary),e.last_name,e.first_name 
from employees e join salaries s on e.emp_no=s.emp_no
where s.to_date='9999-01-01' and 
s.salary not in 
(select max(salary) from salaries where s.to_date='9999-01-01' )

19. 查找所有员工的last_name和first_name以及对应的dept_name

• 题目描述

查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
CREATE TABLE departments (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
PRIMARY KEY (dept_no));
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

• 思路
  1.查找所有员工,包括未分配部门员工，所以用LEFT JOIN;
  2.两次left join后select结果。

• 代码

select e.last_name,e.first_name,d.dept_name from employees e 
left join dept_emp de on e.emp_no=de.emp_no 
left join departments d on de.dept_no=d.dept_no

20.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

• 题目描述

查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

• 思路
  分别找到emp_no为10001员工当前薪水和入职薪水，做差即为growth

• 代码
  方法一 ：where筛选

select max(salary)-min(salary) as growth 
from salaries
where emp_no = 10001;

方法二：group by

select max(salary) - min(salary)
from salaries
group by emp_no
having emp_no = 10001;