牛客SQL1-20题

SQL1

查找最晚入职员工的所有信息,现在给一张员工表

注意：这个业务有可能存在最晚入职员工有多个的情况，OLAP场景


//考虑最晚的员工如果不止一个的情况
//先找到最晚的hire_data，再用条件查到
SELECT * FROM employees
WHERE hire_date =(SELECT MAX(hire_date) FROM employees)

//不考虑最晚入职员工有多个的情况，降序排序，并且只显示第一行  
// limit(x,y)代表第x行开始共显示y行  limit n代表从第0行开始显示n行
SELECT * FROM employees
ORDER BY hire_date DESC LIMIT 0,1 //当然这里LIMIT 1也行

SQL2

查找入职时间排倒数第三的员工所有信息,现在给一张员工表

注意：这个业务有可能存在入职时间排名倒数第三员工有多个的情况，OLAP场景

//考虑入职时间排名倒数第三员工有多个的情况
//方法同SQL1
//注意因为入职时间相同的员工有多个，所以查倒数第三入职时间需要DISTINCT去重
SELECT * FROM employees
WHERE hire_date = (SELECT DISTINCT hire_date FROM employees
ORDER BY hire_date DESC LIMIT 2,1)

//不考虑同一时间多个入职的情况
//方法同SQL1
SELECT * FROM employees
ORDER BY hire_date DESC LIMIT 1 OFFSET 2

SQL3

查找各个部门当前领导的薪水详情以及其对应部门编号dept_no,现在给一张员工薪水表和一张各个部门的领导表

//很显然，需要对两张表进行连接查询
//where和inner join的效果是一样的，但是尽量用inner join性能好
SELECT s.*,d.dept_no
FROM salaries s
INNER JOIN dept_manager d ON s.emp_no = d.emp_no
order by emp_no

SQL4

查找所有已经分配部门的员工的last_name和first_name以及dept_no,这里给到两张表，一张员工表，一张部门表

注意：存在员工没有分配部门的情况，需要用内连接或者自然连接去除

SELECT last_name,first_name,dept_no
FROM employees e
INNER JOIN dept_emp d
ON e.emp_no = d.emp_no

SQL5

查找所有员工的last_name和first_name以及对应部门编号dept_no,与上一题的区别在于，这题需要把没有分配部门的员工信息也显示出来，很明显这里需要用左/右连接了

/* INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。*/
//这里当没有匹配项时要显示员工表的信息，所以用左连接
SELECT last_name,first_name,dept_no
FROM employees e
LEFT JOIN dept_emp d
ON e.emp_no = d.emp_no

SQL7

查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t,现在给一张薪水表

//很显然，这里要求的是每个员工的薪水记录，所以要对员工分组聚合查询，groupby+count
//并且需要对聚合后的结果进行筛选，需要使用having
//小技巧：select后聚合的属性可起别名，用在having筛选中
SELECT emp_no,COUNT(emp_no) t
FROM salaries
GROUP BY emp_no
HAVING t > 15

SQL10

获取所有非manager的员工emp_no

//用左连接判断是否有员工不是领导
SELECT e.emp_no
FROM employees e
LEFT JOIN dept_manager d
ON e.emp_no = d.emp_no
WHERE d.emp_no IS NULL

//不包含用 NOT IN(...)
SELECT emp_no FROM employees
WHERE emp_no NOT IN(
  SELECT emp_no FROM dept_manager
)

SQL11

获取所有员工当前的manager

注意两个细节

题目中要求当前/现在，需要把to_date固定，员工走了或者领导走了都不显示
如果员工是经理则不显示，意思是两个属性不相等
两张表肯定要连接，那么基于哪个属性连接呢？这里要求的是员工对应的部门领导，所以要对部门连接

SELECT e.emp_no,m.emp_no
FROM dept_emp e
INNER JOIN dept_manager m
ON e.dept_no = m.dept_no
WHERE e.emp_no != m.emp_no && e.to_date = '9999-01-01' && m.to_date = '9999-01-01'

SQL12

获取每个部门中当前员工薪水最高的相关信息
经典问题：select非聚合字段

错误示范：

//错误示范
SELECT dept_no,d.emp_no,MAX(salary) maxSalary
FROM dept_emp d
INNER JOIN salaries s
ON d.emp_no=s.emp_no
GROUP BY dept_no
HAVING d.dept_no = '9999-01-01'
ORDER BY dept_no

d.emp_no是非聚合字段，不能出现在SELECT。因为一个聚合字段(dept_no)对应多个非聚合字段(emp_no)，所以选择的时候，会随机选择非聚合字段中的任何一个，于是出错
为了避免出现问题一，如果强行用HAVING salary=MAX(s.salary)在分组记录中筛选，也会报错，having不能进行属性的比较筛选，一般和聚合函数联用
如果这题不需要显示emp_no，就可以使用JOIN+GROUPBY

正确答案：

//先求每个部门最高薪水的表，然后求部门、员工、薪水的表，两张表连接查询
SELECT t1.dept_no,t1.emp_no, maxSalary
FROM (
  SELECT d1.emp_no,dept_no,salary
  FROM dept_emp d1
  INNER JOIN salaries s1
  ON d1.emp_no=s1.emp_no
  WHERE d1.to_date='9999-01-01' AND s1.to_date='9999-01-01'
) t1
INNER JOIN (
  SELECT dept_no,MAX(salary) maxSalary
  FROM dept_emp d2
  INNER JOIN salaries s2
  ON d2.emp_no = s2.emp_no
  WHERE d2.to_date='9999-01-01' AND s2.to_date='9999-01-01'
  GROUP BY dept_no
) t2
ON t1.dept_no=t2.dept_no AND t1.salary=t2.maxSalary
ORDER BY t1.dept_no

SQL16

统计各个title类型对应的员工薪水和平均工资

这题考查：对分组聚合后的结果进行排序

SELECT title,AVG(salary)
FROM titles t
INNER JOIN salaries s
ON t.emp_no=s.emp_no
WHERE t.to_date='9999-01-01' AND s.to_date='9999-01-01'
GROUP BY title
ORDER BY AVG(salary)

SQL17

获取薪水第二多的员工的编号和对应的薪水

注意：多个员工的薪水为第二多的薪水的情况

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
)
ORDER BY emp_no ASC

SQL18

查找薪水排名第二多的员工编号、薪水、日期(禁用order by)

什么怪题，不能用order by，而且要注意可能存在排名第二有多个员工的情况，那只能先查最高，然后筛掉最高查次高，然后用次高作为条件查询

SELECT e.emp_no,salary,last_name,first_name
FROM employees e
INNER JOIN salaries s
ON e.emp_no=s.emp_no
WHERE salary=(
  SELECT MAX(salary)
  FROM salaries
  WHERE salary < (
    SELECT MAX(salary)
    FROM salaries
    WHERE to_date='9999-01-01'
  ) AND to_date='9999-01-01'
) AND to_date='9999-01-01'

SQL19

查找所有员工的last_name、first_name和dept_name

题目要求没有分配部门的员工显示部门名为NULL，显然是左连接

1
2
3

SELECT last_name,first_name,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