【MySQL】牛客刷题篇

SQL3 查找薪水及部门(多表查询)

题目3：查找薪水及部门

where合并记录

SELECT salaries.emp_no, salaries.salary, salaries.from_date, salaries.to_date, dept_manager.dept_no
FROM salaries, dept_manager
WHERE salaries.emp_no = dept_manager.emp_no
ORDER BY salaries.emp_no;

INNOR JOIN

SELECT s.*, d.dept_no
FROM dept_manager as d INNER JOIN salaries as s ON d.emp_no = s.emp_no
ORDER BY s.emp_no;

SELECT salaries.*, dept_manager.dept_no
FROM dept_manager INNER JOIN salaries ON dept_manager.emp_no = salaries.emp_no
ORDER BY salaries.emp_no;

SQL5 查询员工的姓名及部门

查询员工的姓名及部门

使用外部联结的左联结。
内联结，两边表同时有对应的数据，即任何一边缺失数据就不显示。
左联结，读取左边数据表的全部数据，即便右边表无对应数。即右表d中dept_no即使为NULL，也会读取左表e中的全部emp。

LEFT JOIN

left join是left outer join的缩写

left join: 包含左表的所有行，对应的右表行可能为空。
right join: 包含右表的所有行，对应的左表行可能为空。

SELECT em.last_name, em.first_name, dp.dept_no
FROM employees AS em LEFT JOIN dept_emp AS dp
ON em.emp_no = dp.emp_no;

SQL7 查找薪水记录超过15次的

分组+统计个数

mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |
|  10001 |  76884 | 1995-06-24 | 1996-06-23 |
|  10001 |  80013 | 1996-06-23 | 1997-06-23 |
|  10001 |  81025 | 1997-06-23 | 1998-06-23 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10002 |  72527 | 1996-08-03 | 1997-08-03 |
+--------+--------+------------+------------+
18 rows in set (0.00 sec)

SELECT emp_no, COUNT(emp_no) AS t
FROM salaries
GROUP BY emp_no HAVING t > 15;

SQL10 获取所有非 manager的ID

WHERE dp.dept_no is NULL

SELECT em.emp_no
FROM employees AS em LEFT JOIN dept_manager AS dp
ON em.emp_no = dp.emp_no
WHERE dp.dept_no is NULL;

SQL11 获取所有员工当前的manager

INNER JOIN

SELECT em.emp_no, dp.emp_no AS manager
FROM dept_emp AS em INNER JOIN dept_manager AS dp
ON em.dept_no = dp.dept_no
AND em.emp_no != dp.emp_no;

WHERE

SELECT em.emp_no, dp.emp_no AS manager
FROM dept_emp AS em , dept_manager AS dp
WHERE em.dept_no = dp.dept_no
AND em.emp_no != dp.emp_no;