牛客SQL刷题总结（三）

1. 获取薪水第二高的员工的相关信息
   

   -- 方法一：使用窗口函数
   SELECT emp_no, salary
   FROM (SELECT *,
       	dense_rank() over(order by salary DESC) rnk
   	FROM salaries)tmp
   WHERE rnk = 2 and to_date='9999-01-01';
   
   -- 方法二: 使用子查询（擅用GROUP BY）
   SELECT emp_no, salary
   FROM salaries
   WHERE salary = (SELECT salary 
                   FROM salaries
                   GROUP BY salary
                   ORDER BY salary LIMIT 1,1)
   AND to_date = '9999-01-01';
   
   -- 方法三：(不使用ORDER BY)
   SELECT emp_no, salary
   FROM salaries
   WHERE salary = (SELECT MAX(salary)
                  FROM salaries
                  WHERE salary < (SELECT MAX(salary)
                                 FROM salaries
                                 WHERE to_date = '9999-01-01')
                  AND to_date = '9999-01-01');
                  
   -- 方法四：(不使用ORDER BY)
   SELECT emp_no, salary
   FROM salaries
   WHERE salary = (SELECT s1.salary FROM salaries s1
                  INNER JOIN salaries s2
                  ON s1.salary <= s2.salary
                  AND s1.to_date = '9999-01-01'
                  AND s2.to_date = '9999-01-01'
                  GROUP BY s1.salary
                  HAVING COUNT(DISTINCT s2.salary) = 2);
   

2. 查找所有员工自入职以来的薪水涨幅情况
   

   -- 方法一：
   SELECT DISTINCT a.emp_no, (a.salary - b.salary) AS growth
   FROM salaries AS a, salaries AS b
   WHERE a.salary = (SELECT salary
   				  FROM salaries AS s
   				  WHERE s.emp_no=a.emp_no
   				  ORDER BY to_date DESC LIMIT 1)
   AND b.salary = (SELECT salary
   			    FROM salaries AS s
   				WHERE s.emp_no=a.emp_no
   				ORDER BY to_date ASC LIMIT 1)
   AND a.emp_no=b.emp_no 
   AND a.to_date = '9999-01-01'
   ORDER BY growth ASC;
   
   -- 方法二：
   SELECT a.emp_no, (b.salary - c.salary) AS growth
   FROM
   	employees AS a
       INNER JOIN salaries AS b
       ON a.emp_no = b.emp_no AND b.to_date = '9999-01-01'
       INNER JOIN salaries AS c
       ON a.emp_no = c.emp_no AND a.hire_date = c.from_date 
   ORDER BY growth ASC;
   

3. 排序问题

   -- 方法一：使用窗口函数（不要忘了对rank进行排序）
   SELECT emp_no, salary,
         dense_rank() over (order by salary desc) AS rank
   FROM salaries
   WHERE to_date = "9999-01-01"
   ORDER BY rank, emp_no
   
   -- 方法二：不使用窗口函数
   SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank
   FROM salaries AS s1 INNER JOIN salaries AS s2
   ON s1.salary <= s2.salary
   WHERE s1.to_date = '9999-01-01'  AND s2.to_date = '9999-01-01'
   GROUP BY s1.emp_no
   ORDER BY s1.salary DESC, s1.emp_no ASC;
   

4. 获取员工当前的薪水比其manager当前薪水还高的相关信息
   

   -- 方法一：
   SELECT E.emp_no AS emp_no, M.emp_no AS manager_no, E.salary AS emp_salary, M.salary AS manager_salary
   FROM (SELECT s.salary, s.emp_no, de.dept_no 
         FROM salaries s 
         INNER JOIN dept_emp de 
         ON s.emp_no = de.emp_no 
         AND s.to_date = '9999-01-01') E
   INNER JOIN (SELECT s.salary, s.emp_no, dm.dept_no 
               FROM salaries s 
               INNER JOIN dept_manager dm 
               ON s.emp_no = dm.emp_no 
               AND s.to_date = '9999-01-01') AS M
   ON E.dept_no = M.dept_no AND E.salary > M.salary;
   
   -- 方法二：
   SELECT S1.emp_no AS emp_no, M.emp_no AS manager_no, S1.salary AS emp_salary, S2.salary AS manager_salary
   FROM salaries AS S1
   INNER JOIN dept_emp AS E
   ON S1.emp_no = E.emp_no
   INNER JOIN dept_manager AS M
   ON E.dept_no = M.dept_no
   INNER JOIN salaries AS S2
   ON S2.emp_no = M.emp_no
   WHERE S1.to_date='9999-01-01' 
   AND S2.to_date='9999-01-01'
   AND S1.salary > S2.salary;
   

5. 每年薪水涨幅超过5000的员工
   

   SELECT s1.emp_no, s2.from_date,(s2.salary-s1.salary) AS salary_growth
   FROM salaries s1
   INNER JOIN salaries s2
   ON s1.emp_no = s2.emp_no AND s1.to_date = s2.from_date
   WHERE s2.salary-s1.salary > 5000
   ORDER BY salary_growth DESC;