题解 | #统计出当前各个title类型对应的员工当前薪水对应的平均工资#
获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
http://www.nowcoder.com/practice/c1472daba75d4635b7f8540b837cc719
SQL18_获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
方法一 多层max嵌套
SELECT e.emp_no, s.salary, e.last_name, e.first_name FROM employees AS e INNER JOIN salaries AS s ON e.emp_no = s.emp_no WHERE s.salary = ( SELECT max( salary ) FROM ( SELECT salary FROM salaries AS s1 WHERE s1.salary < ( SELECT max( salary ) FROM salaries ) ) AS m )
方法二 通用型求任意第几高
SELECT e.emp_no, s.salary, e.last_name, e.first_name FROM employees e JOIN salaries s ON e.emp_no = s.emp_no AND s.salary = ( SELECT # 选出第二大的值 * FROM # 连接时每一项s2的值会对应满足条件的s1的值 salaries s1 # 所以s2所对应的值的行数就表示有几条小于等于该工资 JOIN salaries s2 ON s1.salary <= s2.salary # 第二大就对应两行 GROUP BY s1.salary # 要用s1的工资来分类才能统计出s2对应的条数,数的本来就是s1的行数 HAVING count( DISTINCT s2.salary )= 2 # 考虑到有重复的工资,故要用distinct )