查询每个部门工资最高的前两名的姓名和部门名称
第一种解决方案:第二种解决方案:SQL练习题:
假设一个部门有4个人,A,B,C,D
A的工资=10000
B的工资=9000
C的工资=8000
D的工资=7000
问题:现在要找出这个部门工资最高的两个人
第一种解决方案:
SELECT e.*,d.* FROMemp e WHERE (SELECT COUNT(*) FROMemp p WHERE e.dept2 = p.dept2 AND e.incoming < p.incoming) < 2 ORDER BY sid,e.incoming ;
方案解析:
根据题目的要求分析,可以得出下面两条结论,满足这两条结论的人就是要找的前两人:
1,比第一高工资高的人为0个,count的数量<2
2,比第二高工资高的人为1个,count的数量<2
那么现在开始比较(e.incoming < p.incoming),
1,A和部门里面所有的人进行对比,并对比对的结果进行统计。
A<A false
A<B false
A<C false
A<D false
统计结果,count的数量的为0
那么根据上面的结论 A满足第一条结论。
2,B和部门里面所有的人进行对比,并对比对的结果进行统计。
B<A true
B<B false
B<C false
B<D false
统计结果,count的数量为1
那么根据上面的结论 B满足第二条结论。
3,C和部门里面所有的人进行对比,并对比对的结果进行统计。
C<A true
C<B true
C<C false
C<D false
统计结果,count的数量为2
那么根据上面的结论 C不满足两条结论。
4,D和部门里面所有的人进行对比,并对比对的结果进行统计。
D<A true
D<B true
D<C true
D<D false
统计结果,count的数量为3
那么根据上面的结论 D不满足两条结论。
那么最终比对完成后满足结论的就只有A和B
第二种解决方案:
SELECT bb.* FROM (SELECT (@j:=@j+1) pm,s.* FROM emp s,(SELECT @j:=0)t GROUP BY sid ORDER BY dept2 ASC ,incoming DESC)bbINNER JOIN (SELECT tt.dept2,MIN(tt.pm) pm FROM (SELECT (@i:=@i+1) pm,s.* FROM emp s,(SELECT @i:=0)t GROUP BY sid ORDER BY dept2 ASC ,incoming DESC)tt GROUP BY tt.dept2)cc ON cc.dept2 = bb.dept2 WHERE bb.pm < cc.pm + 2
方案解析:
1,添加排名伪列
例如:
执行如下SQL:
SELECT (@j:=@j+1) pm,s.* FROM emp s,(SELECT @j:=0)t GROUP BY sid ORDER BY dept2 ASC ,incoming DESC
得到临时bb表
2,求出一个部门排名第一的
执行如下SQL:
SELECT tt.dept2,MIN(tt.pm) pm FROM (SELECT (@i:=@i+1) pm,s.* FROM emp s,(SELECT @i:=0)t GROUP BY sid ORDER BY dept2 ASC ,incoming DESC)tt GROUP BY tt.dept2
得到临时cc表
3,临时表对比排名求出结果(WHERE bb.pm < cc.pm + 2)
这里+2的目的就是要求前几名就+多少。比如前两名就+2,前三名就+3
最终结果: