sql子查询问题 我要查询每个部门工资前3名的怎么写sql? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 SELECTb.*FROM (SELECT DISTINCT bumen FROM staff) AS aCROSS APPLY(SELECT TOP 3 * FROM staff WHERE bumen=a.bumen ORDER BY salary desc) AS b 或SELECT * FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY bumen ORDER BY salary DESC) AS RN FROM staff) AS t WHERE RN<=3 上面一个部门只显示<=3位,当salary相同时,要并列连续或断时可用DENSE_RANK/RANK 这个是oracle的吗,我在mysql运行不了。 mysql中要先排号,然后再把前3个取出来 。 MYSQL e.g.select * from (select *,@id:=if(`bumen`=@bumen,@id+1,1) as RN,@bumen:=`bumen` from J1 order by bumen,salary desc) as t where RN<=3 ; SELECT * FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY bumen ORDER select a.* from t1 left t2 on t1.bumen=t2.bumen and t1.salary < t2.salary group by t1.name,t1.bumen,t1.salary having count(1)<3 select * from staff awhere 3>(select count(1) from staff where bumen=a.bumen and salary<a.salary)ORDER BY a.salary desc select * from staff awhere 3>(select count(1) from staff where bumen=a.bumen and salary>a.salary)ORDER BY a.salary desc 参考下贴中的多种方法http://blog.csdn.net/acmain_chm/article/details/4126306[征集]分组取最大N条记录方法征集,及散分.... 如何将sql2005的数据库 转成 mysql的? 如何把EXCEL文件导入到MYSQL,在线急等。 如何用一条语句drop掉所有aa为前缀的表 这个查询可以优化吗? mysql enterprise monitor 中不显示CPU,内存信息 高手说下这个查询语句为何不对 以前从来没有出现过的问题,这两天没用linux,今天一进去,mysq突然报错?晕! 如何用一条语句查询多表的记录数? 请教:Mysql中关闭数据库的命令是什么? 一段关于游标的简单代码 Qsql数据库问题 Mysql字符乱码问题
b.*
FROM (SELECT DISTINCT bumen FROM staff) AS a
CROSS APPLY(SELECT TOP 3 * FROM staff WHERE bumen=a.bumen ORDER BY salary desc) AS b
可用DENSE_RANK/RANK
mysql中要先排号,然后再把前3个取出来 。
e.g.select * from (select *,@id:=if(`bumen`=@bumen,@id+1,1) as RN,@bumen:=`bumen` from J1 order by bumen,salary desc) as t where RN<=3 ;
where 3>(select count(1) from staff where bumen=a.bumen and salary<a.salary)
ORDER BY a.salary desc
where 3>(select count(1) from staff where bumen=a.bumen and salary>a.salary)
ORDER BY a.salary desc
[征集]分组取最大N条记录方法征集,及散分....