牛客SQL-employees表（一）：195-220

195. 查找employees里最晚入职员工的所有信息

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

/*
# 如果员工入职的日期都不是同一天，可以对时间用 order by 按降序排列，则第一个就是最晚的，用 limit 取第一个
SELECT *
FROM employees
ORDER BY hire_date DESC
LIMIT 1
*/

196. 查找employees里入职员工时间排名倒数第三的员工所有信息

SELECT *
FROM employees
WHERE hire_date = (
    SELECT hire_date
    FROM employees
    GROUP BY hire_date # 1. 分组
    ORDER BY hire_date DESC # 2. 倒序排列
    LIMIT 1 OFFSET 2 -- LIMIT 2, 1 # 3. 跳过前两组取单独第三组
)

/*
1. LIMIT 和 OFFSET 一起使用时，LIMIT 后面的值是要取的个数，OFFSET 后面的值则是要跳过的个数
2. 实际上，SELECT 后面直接写字段性能更好，用 * 效率要低那么一点，因为数据库要解析 * 去基表里查找所有的列，然后再 SELECT 这些列
*/

197. 查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列

/* 只会用 LEFT JOIN 的版本
SELECT t1.*, t2.dept_no
FROM salaries t1 LEFT JOIN dept_manager t2 USING(emp_no)
WHERE t1.emp_no = t2.emp_no
ORDER BY t1.emp_no
*/

SELECT t1.*, t2.dept_no
FROM salaries t1 INNER JOIN dept_manager t2 ON t1.emp_no = t2.emp_no
    AND t1.to_date = '9999-01-01'
    AND t2.to_date = '9999-01-01' -- 其实不加这两个限定条件也能顺利提交，而且这两句也能放在 WHERE 里；但根据实际经验，要注意让查的是当前薪水，所以一定不要忘了要对salaries中的字段写上限定条件
ORDER BY t1.emp_no

/*
考察点：三个JOIN
1. LEFT JOIN：返回左表中的所有记录 & 右表中的联接字段相等的记录
2. RIGHT JOIN：返回右表中的所有记录 & 左表中的联接字段相等的记录
3. INNER JOIN：仅返回两表中联接字段相等的记录
*/

198. 查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示

SELECT t1.last_name, t1.first_name, t2.dept_no
FROM employees t1 INNER JOIN dept_emp t2 USING(emp_no)
# 考察点：左联接（LEFT JOIN）

199. 查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工

SELECT last_name, first_name, dept_no
FROM employees t1 LEFT JOIN dept_emp t2 USING(emp_no)
# 考察点：内联接（INNER JOIN）

201. 查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

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

202. 找出所有员工具体的当前薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

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

204. 找出所有非部门领导的员工emp_no

SELECT emp_no
FROM employees t1 LEFT JOIN dept_manager t2 USING(emp_no)
WHERE t2.dept_no IS NULL

/*
# 另一种解法。但是由于用 NOT IN 会转化成多表连接，而且不使用索引，效率更低
SELECT emp_no
FROM employees t1 LEFT JOIN dept_manager t2 USING(emp_no)
WHERE t1.emp_no NOT IN (
    SELECT emp_no
    FROM dept_manager
)
*/

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

/*
# 自己写的不严谨的能提交的版本
SELECT t1.emp_no, t2.emp_no AS manager
FROM dept_emp t1 LEFT JOIN dept_manager t2 USING(dept_no)
WHERE t1.emp_no != t2.emp_no
*/

SELECT t1.emp_no, t2.emp_no AS manager
FROM dept_emp t1 INNER JOIN dept_manager t2 -- 根据题目要求，最好用 INNER JOIN
    ON t1.dept_no = t2.dept_no AND t1.emp_no <> t2.emp_no -- 最好用 <> 而非 !=
WHERE t1.to_date - '9999-01-01'
    AND t2.to_date - '9999-01-01'

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

SELECT dept_no, emp_no, salary AS maxSalary
FROM ( # 查找每个部门的最高工资，得到含有dept_no、emp_no、薪水倒序排列的临时表
    SELECT
        t1.dept_no,
        t2.emp_no,
        t2.salary,
        RANK() OVER (PARTITION BY t1.dept_no ORDER BY t2.salary DESC) AS ranking -- 要用 rank 或者 dense rank ，row number 排序是1234，找不到并列第一
    FROM dept_emp t1 INNER JOIN salaries t2 USING(emp_no)
    WHERE t1.to_date = '9999-01-01'
        AND t2.to_date = '9999-01-01'
) a
WHERE ranking = 1
ORDER BY dept_no

常用的排序窗口接口：

• row_number（按顺序编号——1,2,3,4,5）

• rank（按照规则排名。遇到同分，它是跳跃的、间断的排名，例如两个并列第一名后，下一个是第三名——1,2,2,4,5）

• dense_rank（按照规则排名。排序数字是连续的、不间断。当有相同的分数时，它们的排名结果是并列的——1,2,2,3,4）

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

SELECT *
FROM employees
WHERE emp_no % 2 = 1
    AND last_name != 'Mary'
ORDER BY hire_date DESC

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

/*
SELECT title, AVG(s.salary) avg_salary
FROM titles t LEFT JOIN salaries s USING(emp_no)
WHERE t.to_date = '9999-01-01' AND s.to_date = '9999-01-01' -- 满足“当前”条件
GROUP BY title
ORDER BY avg_salary
*/

# 也可以用开窗函数，此时要对 title 去重
SELECT DISTINCT title, AVG(salary) OVER (PARTITION BY title) AS avg_salary
FROM titles t INNER JOIN salaries s USING(emp_no) -- 用内联接比较严谨
WHERE t.to_date = '9999-01-01' AND s.to_date = '9999-01-01' -- 满足“当前”条件
ORDER BY avg_salary

211. 获取薪水第二多的员工的emp_no以及其对应的薪水salary，

若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，并按emp_no升序排序。

# 解法1：窗口函数
SELECT emp_no, salary
FROM (
    SELECT
        emp_no,
        salary,
        DENSE_RANK() OVER(ORDER BY salary DESC) AS ranking -- 别忘了降序排列。不需用 PARTIRION BY 分组
    FROM salaries
    WHERE to_date = '9999-01-01'
) t
WHERE ranking = 2
ORDER BY emp_no

/*
# 解法2：不理会“当前”，不嵌套的方法
SELECT emp_no, salary
FROM salaries
WHERE salary = (
    SELECT DISTINCT salary
    FROM salaries
    ORDER BY salary DESC -- 别忘了降序排列
    LIMIT 1 OFFSET 1 -- LIMIT 1, 1
)


# 如果只取一个人
SELECT emp_no, salary
FROM salaries
WHERE to_date = '9999-01-01'
ORDER BY salary DESC
LIMIT 1, 1

212. 查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用 Order By 完成

# JOIN() 自交，利用 COUNT() 函数来获得排名
SELECT t2.emp_no, t2.salary AS salary, t1.last_name, t1.first_name
FROM employees t1 INNER JOIN salaries t2 USING(emp_no)
WHERE t2.to_date = '9999-01-01'
    AND t2.salary = (
        SELECT a.salary
        FROM salaries a JOIN salaries b -- 自联接查询
            ON a.salary <= b.salary
            AND a.to_date = '9999-01-01'
            AND b.to_date = '9999-01-01'
        GROUP BY a.salary  -- 当 a <= b 联接且以 a.salary 分组时，a 会对应多个 b
        HAVING COUNT(DISTINCT b.salary) = 2 -- 对每个 a.salary(salary<=s2.salary) 进行计数，去重之后的数量就是对应的名次
    )

/*
# 异曲同工
SELECT t2.emp_no, t2.salary AS salary, t1.last_name, t1.first_name
FROM employees t1 INNER JOIN salaries t2 USING(emp_no)
WHERE t2.to_date = '9999-01-01'
    AND t2.emp_no = (
        SELECT a.emp_no
        FROM salaries a JOIN salaries b
            ON a.salary <= b.salary
            AND a.to_date = '9999-01-01'
            AND b.to_date = '9999-01-01'
        GROUP BY a.emp_no
            HAVING COUNT(1) = 2 -- 也就是 HAVING COUNT(DISTINCT b.salary) = 2
    )


# 异曲同工
SELECT t2.emp_no, t2.salary AS salary, t1.last_name, t1.first_name
FROM employees t1 INNER JOIN salaries t2 USING(emp_no)
WHERE t2.to_date = '9999-01-01'
    AND 1 = (
        SELECT COUNT(DISTINCT salary)
        FROM salaries t3
        WHERE t2.salary < t3.salary
            AND t2.to_date = '9999-01-01'
            AND t3.to_date = '9999-01-01'
    )


# MAX() 函数，先除去最高的一个，在再剩下的数据里面取最高，就是第二高 
SELECT t2.emp_no, t2.salary AS salary, t1.last_name, t1.first_name
FROM employees t1 INNER JOIN salaries t2 ON t1.emp_no = t2.emp_no
AND  t2.to_date='9999-01-01'
AND t2.salary = ( # 3. 将第二高工资作为查询条件
    SELECT MAX(salary) -- # 2. 查询除了原表最高工资以外的最高工资(第二高工资)
    FROM salaries
    WHERE salary < (
        SELECT MAX(salary) # 1. 查出原表最高工资
        FROM salaries 
        WHERE to_date='9999-01-01'
    )
    AND to_date='9999-01-01'
)
*/

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

SELECT last_name, first_name, dept_name
FROM employees t1 LEFT JOIN dept_emp t3 ON t1.emp_no = t3.emp_no
                  LEFT JOIN departments t2 ON t3.dept_no = t2.dept_no

/*
# 利用 NATURAL JOIN
select em.last_name, em.first_name, de.dept_name
from employees as em left join (dept_emp natural join departments) as de
on em.emp_no = de.emp_no


# 主表+副表
SELECT last_name, first_name, dept_name
FROM employees AS e LEFT JOIN (SELECT emp_no, dept_name
                               FROM dept_emp AS de LEFT JOIN departments AS d
                               ON de.dept_no = d.dept_no)k
ON e.emp_no = k.emp_no;
*/

215. 查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

注: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01'时，表示依然在职，无后续调整记录

/* # 错误点主要在于未考虑实际。最大值不一定就是最后一次涨薪后的值，应该考虑降薪问题
# 错误方法1——growth列结果为0
SELECT emp_no, (MAX(salary) - MIN(salary)) AS growth
FROM salaries
WHERE to_date = '9999-01-01'
GROUP BY emp_no
ORDER BY growth

# 错误方法2：
SELECT emp_no, MAX(salary) - MIN(salary) AS growth
FROM salaries
WHERE emp_no IN (
    SELECT DISTINCT emp_no
    FROM salaries
    WHERE to_date = '9999-01-01'
)
GROUP BY emp_no
ORDER BY growth
*/

# 思路：JOIN 现在员工的临时表与员工最早的salary，再做减法
SELECT b.emp_no, (b.salary - a.salary) AS growth
FROM (
    SELECT t2.emp_no, t2.salary
    FROM employees t1 INNER JOIN salaries t2 USING(emp_no)
    WHERE t1.hire_date = t2.from_date
) a -- 入职薪水
INNER JOIN (
    SELECT emp_no, salary
    FROM salaries
    WHERE to_date = '9999-01-01' -- “在职”
) b -- 当前薪水
ON a.emp_no = b.emp_no
ORDER BY growth

216. 统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序

SELECT dept_no, dept_name, COUNT(*) AS sum
FROM dept_emp t2 LEFT JOIN departments t1 USING(dept_no) -- 也可以用 INNER JOIN
                 INNER JOIN salaries t3 USING(emp_no)
GROUP BY dept_no
ORDER BY dept_no

217. 对所有员工的薪水按照salary降序先进行1-N的排名，如果salary相同，再按照emp_no升序排列

SELECT emp_no, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS t_rank
FROM salaries
ORDER BY salary DESC, emp_no -- ORDER BY t_rank, emp_no
# 题目要求如果排名相同就再按照emp_no升序排序，所以最后要利用 ORDER BY 来排序

218. 获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary

SELECT dept_no, emp_no, salary
FROM dept_emp t2 INNER JOIN salaries t4 USING(emp_no)
WHERE t4.to_date = '9999-01-01' -- 注意明确是哪个表中的列
    AND emp_no NOT IN (
        SELECT emp_no
        FROM dept_manager
        WHERE to_date = '9999-01-01'
    )

219. 获取员工其当前的薪水比其manager当前薪水还高的相关信息。第一列给出员工的emp_no，第二列给出其manager的manager_no，第三列给出该员工当前的薪水emp_salary,第四列给该员工对应的manager当前的薪水manager_salary

# 思路：将所有员工表和薪水表联结，再将部门经理表和薪水表联结，两次薪水表分别命名是为了好区分。再将上面两张带着薪水的表左联结（联接条件是部门号相同），此时比较 t2.salary > t4.salary，满足筛选条件的就是员工的薪水大于他部门经理的薪水

SELECT a.emp_no AS emp_no, b.emp_no AS manager_no, a.salary AS emp_salary, b.salary AS manager_salary
FROM (
    SELECT t2.emp_no, salary, dept_no
    FROM dept_emp t1 INNER JOIN salaries t2 ON t1.emp_no = t2.emp_no
        AND t1.to_date = '9999-01-01'
        AND t2.to_date = '9999-01-01'
) a
INNER JOIN (
    SELECT t4.emp_no, salary, dept_no
    FROM dept_manager t3 INNER JOIN salaries t4 ON t3.emp_no = t4.emp_no
        AND t3.to_date = '9999-01-01'
        AND t4.to_date = '9999-01-01'
) b
ON a.dept_no = b.dept_no
AND a.salary > b.salary

/*
SELECT t2.emp_no AS emp_no, t4.emp_no AS manager_no, t2.salary AS emp_salary, t4.salary AS manager_salary
FROM (
    dept_emp t1 INNER JOIN salaries t2 ON t1.emp_no = t2.emp_no
        AND t1.to_date = '9999-01-01'
        AND t2.to_date = '9999-01-01'
) # 不是嵌套查询，不需要命名
INNER JOIN (
    dept_manager t3 INNER JOIN salaries t4 ON t3.emp_no = t4.emp_no
        AND t3.to_date = '9999-01-01'
        AND t4.to_date = '9999-01-01'
)
ON t1.dept_no = t3.dept_no # 但是要注意联接条件
AND t2.salary > t4.salary
*/
AND a.salary > b.salary

220. 汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序，dept_no一样的再按title升序排序

/*
ELECT dept_no, dept_name, title, count
FROM departments t1 INNER JOIN (
    SELECT dept_no, title, COUNT(*) AS count
    FROM dept_emp t2 INNER JOIN titles t3 USING(emp_no)
    WHERE t2.to_date = '9999-01-01' AND t3.to_date = '9999-01-01'
    GROUP BY dept_no, title -- 因为不同部门包含不同title，首先是根据部门分组，然后同一部分再根据title分组
    ORDER BY dept_no, title
) a
USING(dept_no)
*/

# 也可以直接联接三张表
SELECT de.dept_no, d.dept_name, t.title, count(t.title)
FROM departments d
INNER JOIN dept_emp de ON d.dept_no = de.dept_no AND de.to_date = '9999-01-01'
INNER JOIN titles t ON de.emp_no = t.emp_no AND t.to_date = '9999-01-01'
GROUP BY d.dept_no, t.title
ORDER BY d.dept_no, t.title