自连接(a join a)的妙用
牛客题目
select s.emp_no, s.salary, e.last_name, e.first_name
from salaries s join employees e
on s.emp_no = e.emp_no
where s.salary =
(
select s1.salary
from salaries s1 join salaries s2
on s1.salary <= s2.salary
group by s1.salary
having count(distinct s2.salary) = 2
)
and s.to_date = '9999-01-01'
表自连接以后:
当s1<=s2链接并以s1.salary分组时一个s1会对应多个s2
s1 | s2 |
---|
100 | 100 |
98 | 100 |
| 98 |
| 98 |
95 | 100 |
| 98 |
| 98 |
| 95 |
对s2进行去重统计数量, 就是s1对应的排名
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)