SQL刷题笔记day3——第二大值

1题目

我的错误代码：

select emp_no,salary
from salaries
where salary=
(select salary 
from salaries
group by salary
order by salary
limit 1,1 )
order by emp_no asc

正确代码：

select emp_no,salary
from salaries
where salary=
(select salary 
from salaries
group by salary
order by salary desc //要找第二大的，就要降序排列！！！
limit 1,1 )
order by emp_no asc

这里不能用下面这个代码，因为salary<max(salary)会将除开最大值以为的都返回，而不仅仅是第二大的。

select emp_no,salary
from salaries
where salary<
(select max(salary)
from salaries)
order by emp_no asc

但是下面这样就可以了，在salary<max(salary)这里的剩余salary中再计算一次max(salary)，就返回剩余salary的最大值啦——第二大！然后对应salary等于这个返回值就好了。

select emp_no,salary
from salaries
where salary=(select max(salary) #这里的salary是小于最大值的，返回的就是第二大咯
                from salaries
                where salary<(select max(salary) 
                              from salaries 
                             ))
order by emp_no asc

复盘：思路是正确的，但是失误在小细节! 因为可能有多个员工的薪水为第二多的薪水，所以最号的方法是先找出第二大的薪水（我这里是用的where嵌套），然后输出这一薪水对应的所有员工号

2题目

我的错误代码：

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

大神代码：

#解法1 max 任意相同工资人数
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.to_date='9999-01-01'
and s.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'
                )

解法1思路：找出第二大的薪水，思路同题目1，第二大等于（最大值除外的值中的最大值），计算两次max。

#第二种 通用型可以求任意第几高，并且可以求多个形同工资
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.to_date='9999-01-01'
and s.salary = 
(
     select s1.salary
     from salaries s1 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思路：having count(distinct s2.salary)=2 保证了子查询只返回s2具有两个不同工资水平的工资,又s1.salary<=s2.salary ，所以返回的s1.salary中就是第二大的salary。（只是有点小不明白为什么having count(distinct s2.salary)=2 是取的排名前2个工资，那么多工资水平。。。）

复盘：不能用order by排序就不知道咋整了，正经人谁规定不让用order by呀！