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 3000SQL>
----- ----------
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 3000SQL>
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
employerid如果有索引的话,那应该不会慢.
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一样的联机帮助可以查语法的东东,各位大侠推荐一下
可以先用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-10000employerid ,salary建索引用atgc的查询费时54秒
用我的查询费时6秒