SQL练习6-牛客网1-10题

链接：www.nowcoder.com/ta/sql?page…

知识点统计

知识点	题号
子查询	2
JOIN	3, 4, 5, 6, 9, 10
ORDER BY	2, 6, 8
GROUP BY	7
聚集函数 MAX(), COUNT()	1, 7
LIMIT	2
HAVING	7
DISTINCT	8

1. 查找最晚入职员工的所有信息
   employees (emp_no,birth_date,first_name,last_name,gender,hire_date)

SELECT * FROM employees
    WHERE hire_date = (SELECT MAX(hire_date) FROM employees)

注意最晚入职员工可能不只一人，所以先找出最晚入职日期，再用这个日期查询员工信息.

2. 查找入职员工时间排名倒数第三的员工所有信息
   employees (emp_no,birth_date,first_name,last_name,gender,hire_date)

SELECT * FROM employees
    ORDER BY hire_date desc
    LIMIT 2,1

3. 查找当前薪水详情以及部门编号dept_no
   dept_manager (dept_no,emp_no,from_date,to_date)
   salaries (emp_no,salary,from_date,to_date)

SELECT s.*, d.dept_no
    FROM salaries s JOIN dept_manager d 
        ON (d.emp_no=s.emp_no)
    WHERE d.to_date='9999-01-01' AND s.to_date='9999-01-01'

4. 查找所有已经分配部门的员工的last_name和first_name以及dept_no dept_emp (emp_no,dept_no,from_date,to_date)
   employees (emp_no,birth_date,first_name,last_name,gender,hire_date)

内联：

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

使用内联时必须将employees 表放在前面.

左联：

SELECT last_name, first_name, dept_no
    FROM employees e LEFT JOIN dept_emp d 
        ON (e.emp_no=d.emp_no)
    WHERE d.dept_no <> ''

注意非空值的处理办法.

5. 查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工
   dept_emp (emp_no,dept_no,from_date,to_date)
   employees (emp_no,birth_date,first_name,last_name,gender,hire_date)

SELECT last_name, first_name, dept_no
    FROM employees e LEFT JOIN dept_emp d 
        ON (e.emp_no=d.emp_no)

6. 查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序
   employees (emp_no,birth_date,first_name,last_name,gender,hire_date) salaries (emp_no,salary,from_date,to_date)

SELECT s.emp_no, salary
    FROM salaries s JOIN employees e 
        ON (e.emp_no=s.emp_no)
    WHERE s.from_date=e.hire_date
    ORDER BY s.emp_no desc

7. 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t salaries (emp_no,salary,from_date,to_date)

SELECT emp_no,COUNT(from_date) as t
    FROM salaries
    GROUP BY emp_no
    HAVING t>15

我个人觉得应该是

SELECT emp_no,count(from_date)-1 as t
    FROM salaries
    GROUP BY emp_no
    HAVING t>15

因为from_date = hire_date那次不算涨薪，但是调试通不过，看完评论区发现要把-1去掉。这题的逻辑好像是每一条记录都当作是一次涨薪，也不需要判断salary是否相同。

8. 找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
   salaries (emp_no,salary,from_date,to_date)

SELECT DISTINCT salary 
    FROM salaries
    WHERE to_date='9999-01-01'
    ORDER BY salary desc

9. 获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'
   dept_manager (dept_no,emp_no,from_date,to_date) salaries (emp_no,salary,from_date,to_date)

SELECT d.dept_no, d.emp_no, salary
    FROM dept_manager d JOIN salaries s 
        ON (d.emp_no=s.emp_no)
    WHERE d.to_date='9999-01-01' AND s.to_date='9999-01-01'

10. 获取所有非manager的员工emp_no dept_manager (dept_no,emp_no,from_date,to_date)
    employees (emp_no,birth_date,first_name,last_name,gender,hire_date) （1）通过子查询

SELECT emp_no FROM employees
    WHERE emp_no NOT IN (
        SELECT emp_no FROM dept_manager )

（2）左联结

SELECT e.emp_no 
    FROM employees e LEFT JOIN dept_manager d
        ON e.emp_no = d.emp_no
    WHERE dept_no IS NULL

题号	知识点
1	子查询，MAX()
2	ORDER BY 与 LIMIT
3, 4, 5	INNER JOIN, LEFT JOIN
6	JOIN, ORDER BY
7	COUNT(), GROUP BY, HAVING
8	DISTINCT, ORDER BY
9	JOIN
10	子查询 or JOIN