MySQL练习

T21：查找在职员工入职以来的薪水涨幅情况。

描述给了employees表和salaries表。然后里面薪水可能有下降的过程，无法直接使用MAX(salary)-MIN(salary)；其次需要将离职员工去除。（在职的标识to_date='9999-01-01'）

分析：需要选择仍然在职的员工，然后选择入职日期的薪水与当前薪水。其实选择当前薪水就已经确定是在职的员工了，然后选择最小的to_date就能找到其对应的入职薪水。因此只需要使用一个salaries表。

SELECT s_now.emp_no, s_now.salary - s.salary AS groth FROM salaries s,

(SELECT s1.emp_no, MIN(s1.to_date) AS to_date FROM salaries s1
GROUP BY s1.emp_no) AS s_old,

(SELECT s2.emp_no, s2.salary FROM salaries s2 
WHERE s2.to_date = '9999-01-01') AS s_now

WHERE s.emp_no = s_now.emp_no AND s.to_date = s_old.to_date
ORDER BY groth

T22.统计各部门的工资计数

本以为此题也要统计在职的记录，结果是统计有多少条工资，读题很重要！

-- 在职工资数
SELECT dm.dept_no, dm.dept_name, COUNT(salary) FROM departments dm
JOIN dept_emp de ON dm.dept_no = de.dept_no
JOIN salaries s ON s.emp_no = de.emp_no
WHERE de.emp_no IN (SELECT emp_no FROM dept_emp
                   WHERE to_date = '9999-01-01')
GROUP BY de.dept_no;
-- 所有工资数
SELECT dm.dept_no, dm.dept_name, COUNT(salary) FROM departments dm
JOIN dept_emp de ON dm.dept_no = de.dept_no
JOIN salaries s ON s.emp_no = de.emp_no

GROUP BY dm.dept_no
ORDER BY dm.dept_no

T23.要求对所有员工的薪水按照salary降序排名

通过这道题发现了SQL的窗口函数，好神奇！下面是一个挺全的MySQL网页。

题目要求对salary按降序排列，然后根据给的示例，里面对于相同薪水排名相同，其下一个排序紧接着上一个(1,2,2,3,4)因此可以适应DENSE_RANK()如果要求按个数隔开(1,2,2,4,5)那么使用RANK()。

SELECT emp_no, salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS t_rank FROM salaries s
WHERE to_date = '9999-01-01'
ORDER BY salary DESC , emp_no

下面是完整的函数，OVER()相当于给定条件，PARTITION BY相当于GROUP BY给定分组，ORDER BY 是排序。 

DENSE_RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 

T25，求当前薪水中员工比manager高的相关信息。

分析：首先dept_emp表是全体信息表，dept_manager是manager的信息表，而salaries是全体的薪水表，也就是说需要连接员工与salaries表；manager与salaries表；两张表根据部门连接，比较他们的薪水。

而且需要留下员工salary大于manage的salary，所以对e_s表不需要处理里面的manage部分。

SELECT e_s.emp_no, m_s.emp_no, e_s.salary, m_s.salary FROM 

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

JOIN 

(SELECT dept_no, de.emp_no, salary FROM dept_emp de
JOIN salaries s ON de.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01') AS e_s

ON m_s.dept_no = e_s.dept_no
WHERE e_s.salary > m_s.salary 

-- 解答中有一个不用join的做法
SELECT de.emp_no, dm.emp_no, s1.salary, s2.salary FROM dept_manager dm, dept_emp de, salaries s1, salaries s2
WHERE dm.dept_no = de.dept_no  -- 统一部门
AND de.emp_no = s1.emp_no -- 连接salary
AND dm.emp_no = s2.emp_no
AND s1.to_date = '9999-01-01'  -- 当前员工
AND s2.to_date = '9999-01-01'