牛客网Mysql 11~20

牛客网Mysql 11~20

11.获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示

分析：首先找出e表中不在m表中的元素 在通过 e表中的dept_no与m表中的dept_no找到m表中emp_no即为e中emp_no的经理。

SELECT e.emp_no, m.emp_no
FROM dept_emp e, dept_manager m
WHERE e.emp_no != m.emp_no
AND e.dept_no = m.dept_no
AND m.to_date = '9999-01-01';

12.获取所有部门中员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列

分析：首先建立一张dept_no与max（salary）对应的虚拟表，得到该部门下的最高工资，再将得到的虚拟表，dept_emp，salaries做笛卡尔积，根据最大工资与工资相等，部门相等等条件，按照dept_no升序排列。

SELECT e.DEPT_NO, e.EMP_NO, maxs.Maxsalary
FROM (SELECT ee.DEPT_NO , MAX(ss.SALARY) AS Maxsalary
FROM dept_emp ee, salaries ss
WHERE ee.EMP_NO = ss.EMP_NO
GROUP BY ee.DEPT_NO) AS maxs, dept_emp e, salaries s
WHERE maxs.Maxsalary = s.SALARY
AND maxs.dept_no = e.DEPT_NO
AND e.EMP_NO = s.EMP_NO
AND e.TO_DATE = '9999-01-01'
AND s.TO_DATE = '9999-01-01'
ORDER BY e.DEPT_NO ASC ;

15.请你查找employees表所有emp_no为奇数且last_name不为Mary的员工信息，并按照hire_date逆序排列

SELECT * FROM employees
WHERE EMP_NO %2 <>0 
AND LAST_NAME <>'Mary'
ORDER BY HIRE_DATE DESC ;

16.请你统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg，并且以avg升序排序

SELECT t.TITLE , AVG(s.SALARY) AS 'avg(s.salary)'
FROM titles t
INNER JOIN salaries s
ON t.EMP_NO =s.EMP_NO 
GROUP BY t.TITLE 
ORDER BY AVG(s.SALARY ) ASC;

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

分析：考虑到有重复 所以需要用到子查询 得到第二高的工资的值

SELECT s.EMP_NO ,s.SALARY 
FROM salaries s
WHERE s.SALARY =
(SELECT DISTINCT s2.SALARY 
FROM salaries s2
ORDER BY s2.SALARY DESC
LIMIT 1,1);

18. 请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成

1.如果可以使用order by

SELECT e.EMP_NO , s.SALARY ,e.LAST_NAME ,e.FIRST_NAME 
FROM employees e 
JOIN salaries s 
ON e.EMP_NO = s.EMP_NO 
WHERE s.SALARY =(
SELECT DISTINCT s2.SALARY 
FROM salaries s2
ORDER BY s2.SALARY DESC
LIMIT 1,1
);

2. 不能使用order by，查找第二多的员工，可以建立一张虚拟表，用max函数除去第一多的元素，剩下的表中最大的即为第二多的员工信息。

## 虚拟表
SELECT MAX(sec.SALARY)
FROM 
(SELECT e.EMP_NO , s.SALARY ,e.LAST_NAME ,e.FIRST_NAME 
FROM employees e 
JOIN salaries s 
ON e.EMP_NO = s.EMP_NO 
WHERE s.SALARY <>(
SELECT MAX(s2.SALARY) 
FROM salaries s2
)) AS sec;

SELECT ee.EMP_NO , ss.SALARY ,ee.LAST_NAME ,ee.FIRST_NAME 
FROM employees ee 
JOIN salaries ss 
ON ee.EMP_NO = ss.EMP_NO 
WHERE ss.SALARY =(
SELECT MAX(sec.SALARY)
FROM 
(SELECT e.EMP_NO , s.SALARY ,e.LAST_NAME ,e.FIRST_NAME 
FROM employees e 
JOIN salaries s 
ON e.EMP_NO = s.EMP_NO 
WHERE s.SALARY <>(
SELECT MAX(s2.SALARY) 
FROM salaries s2
)) AS sec);

3. 自连接（可以找出第n高的人 暂时没看懂 后面再补

SELECT s1.salary
FROM salaries s1
JOIN salaries s2 ON
s1.salary <= s2.salary
GROUP BY s1.salary
HAVING count(DISTINCT s2.salary)= 2;

请你查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

分析：三表链接 嵌套两个left join

SELECT m.last_name, m.first_name, d.DEPT_NAME
FROM ( SELECT de.DEPT_NO AS dept_no, e.FIRST_NAME , e.LAST_NAME
FROM employees e
LEFT JOIN dept_emp de ON
e.emp_no = de.emp_no) AS m
LEFT JOIN departments d ON
d.DEPT_NO = m.dept_no;