分组排序后，查询出指定条记录数，请教！
有表salary
employerid char;
salary int;
现在要求查出每组employerid中最大的前三条salary(重复的当成多条）；
比如
employerid salary
1 2000
1 3000
1 4000
1 4000
2 2000
2 3000
2 4000
2 5000
要求结果：
employerid salary1 salary2 salary3
1 4000 4000 3000
2 5000 4000 3000
原表索引在employerid上，数据非常多（几百万条）
存储过程也可以，但要求很高的效率。
问题点数：100、回复次数：16Top
1 楼ATGC（花开蝶舞，木秀鸟栖）回复于 2005-01-04 23:32:20 得分 0
SQL> select * from aa;
EMPLO SALARY
----- ----------
1 2000
1 3000
1 4000
1 4000
2 2000
2 3000
2 4000
2 5000
已选择8行。
select employerid,sum(salary1) salary1,sum(salary2) salary2,sum(salary3) salary3 from(
select employerid,decode('1',top3,salary,'') salary1,decode('2',top3,salary,'') salary2,decode('3',top3,salary,'') salary3
from(
select b.employerid,b.salary,top3 from(
SELECT employerid,salary,
row_number()
OVER (PARTITION BY employerid
ORDER BY salary desc NULLS LAST) top3
FROM aa) b
where b.top3<=3))
group by employerid;
EMPLO SALARY1 SALARY2 SALARY3
----- ---------- ---------- ----------
1 4000 4000 3000
2 5000 4000 3000
SQL>Top
2 楼ATGC（花开蝶舞，木秀鸟栖）回复于 2005-01-04 23:39:54 得分 60
刚才随便写的，效率不太好，下面的好一些
select employerid,
max(decode(top3,1,salary,null)) salary1,
max(decode(top3,2,salary,null)) salary2,
max(decode(top3,3,salary,null)) salary3
from (select employerid,salary,
row_number()
over (partition by employerid
order by salary desc nulls last) top3
from aa)
where top3<=3
group by employerid
/
EMPLO SALARY1 SALARY2 SALARY3
----- ---------- ---------- ----------
1 4000 4000 3000
2 5000 4000 3000
Top
3 楼qiaozhiwei（乔）回复于 2005-01-05 09:27:34 得分 0
ATGC(想到南极去看看飞翔的海鸥)的可以Top
4 楼GerryYang（轻尘）回复于 2005-01-05 09:46:55 得分 0
ATGC(想到南极去看看飞翔的海鸥)的可以,
employerid如果有索引的话,那应该不会慢.Top
5 楼liuyi8903（不让疑问伴随）回复于 2005-01-05 10:20:24 得分 0
不错!支持一下!Top
6 楼NinGoo（http://www.NinGoo.net）回复于 2005-01-05 10:35:44 得分 0
学习Top
7 楼NinGoo（http://www.NinGoo.net）回复于 2005-01-05 10:55:50 得分 0
顺便问一下，oracle里取两个数相除的余数该怎么做？找了半天也没找到模运算符Top
8 楼jgyun（小将）回复于 2005-01-05 11:00:04 得分 0
支持！Top
9 楼ORARichard（没钱的日子......）回复于 2005-01-05 11:11:30 得分 0
mod(a,b)Top
10 楼NinGoo（http://www.NinGoo.net）回复于 2005-01-05 11:23:01 得分 0
谢谢。呵呵，在Toad里mod不显亮，我还以为不是，晕
这题在sql server版见过，不过sql server 2000里不能用row_number()，所以我一下子就想到下面的写法，呵呵
select employerid,max(decode('1',mod(rownum,3),salary,'')) salary1,
max(decode('2',mod(rownum,3),salary,'')) salary2,
max(decode('0',mod(rownum,3),salary,'')) salary3
from
(
select employerid,salary from t1 a where (select count(1) from t1 where employerid=a.employerid)>=3
and (select count(1) from t1 where employerid=a.employerid and salary>a.salary )<3
)
group by employerid
在网上找了找，还是row_number()的效率高，多学一招，开心~~~~~~~~~
用oracle写sql写的太少，很多都不知道，还不知道到哪里查。不知道oracle有没有sql server一样的联机帮助可以查语法的东东，各位大侠推荐一下Top
11 楼topikachu（皮皮）回复于 2005-01-06 13:28:27 得分 40
row_number()效率太低了
可以先用rank()找出小范围的纪录
然后再用row_number
select employerid ,salary,top3 from(
select employerid ,salary,row_number()over (partition by employerid order by rank3 ) top3
from (
select employerid ,salary,rank3 from(
select employerid ,salary,rank()over(partition by employerid order by salary desc) as rank3 from xxx)
where rank3<=3
)
)
where top3<=3
外面再用decode嵌一层就完成要求了
测试用10w条纪录，oracle9i,ibm t30笔记本
employerid 1-10
每个employerid 的salary 1-10000
employerid ,salary建索引
用atgc的查询费时54秒
用我的查询费时6秒Top
12 楼ATGC（花开蝶舞，木秀鸟栖）回复于 2005-01-06 20:13:45 得分 0
奇怪呢，好像楼主没有出现过，也不来讨论讨论。。Top
13 楼baojianjun（包子）回复于 2005-01-08 13:26:12 得分 0
哈哈 ， 海鸥你耐心等等吧，
樓主或許在忙呢Top
14 楼ATGC（花开蝶舞，木秀鸟栖）回复于 2005-01-08 15:53:03 得分 0
等不及啊，俺要等楼主给分买米下锅呢～～～Top
15 楼charmgjj（杰）回复于 2005-01-08 21:57:13 得分 0
值得学习Top
16 楼nanci（☆精灵☆）回复于 2005-01-16 13:14:36 得分 0
实在是报谦，前阵子太忙了。。。
感谢ATGC(想到南极去看看飞翔的海鸥) 。。。
不过rank的速度的确要快些，感谢topikachu(皮皮) 。。。
Top
