# Oracle排名函数运用实例

2014/01/07 17:55

1.

2.

create table TB_PERFORMANCE
(
id NUMBER,
pyear NUMBER,
pmonth NUMBER,
salary NUMBER(15,2),
emplid NUMBER

)

--第一个需求
select *
from (select a.emplid,
a.pmonth,
sum(a.salary),
DENSE_RANK() OVER(partition by pmonth order by sum(a.salary) desc) mr1
from TB_PERFORMANCE a
where pyear = 2013
group by a.emplid, pmonth) t
where emplid = 1001;

--第二个需求
select pyear,'绩效：'||sum(decode(pmonth, 1, salary, 0))||',排名：'||sum(decode(pmonth, 1, mr1, 0)) "1月",
'绩效：'||sum(decode(pmonth, 2, salary, 0))||',排名：'||sum(decode(pmonth, 2, mr1, 0)) "2月",
'绩效：'||sum(decode(pmonth, 3, salary, 0))||',排名：'||sum(decode(pmonth, 3, mr1, 0)) "3月",
'绩效：'||sum(decode(pmonth, 4, salary, 0))||',排名：'||sum(decode(pmonth, 4, mr1, 0)) "4月",
'绩效：'||sum(decode(pmonth, 5, salary, 0))||',排名：'||sum(decode(pmonth, 5, mr1, 0)) "5月",
'绩效：'||sum(decode(pmonth, 6, salary, 0))||',排名：'||sum(decode(pmonth, 6, mr1, 0)) "6月",
'绩效：'||sum(decode(pmonth, 7, salary, 0))||',排名：'||sum(decode(pmonth, 7, mr1, 0)) "7月",
'绩效：'||sum(decode(pmonth, 8, salary, 0))||',排名：'||sum(decode(pmonth, 8, mr1, 0)) "8月",
'绩效：'||sum(decode(pmonth, 9, salary, 0))||',排名：'||sum(decode(pmonth, 9, mr1, 0)) "9月",
'绩效：'||sum(decode(pmonth, 10, salary, 0))||',排名：'||sum(decode(pmonth, 10, mr1, 0)) "10月",
'绩效：'||sum(decode(pmonth, 11, salary, 0))||',排名：'||sum(decode(pmonth, 11, mr1, 0)) "11月",
'绩效：'||sum(decode(pmonth, 12, salary, 0))||',排名：'||sum(decode(pmonth, 12, mr1, 0)) "12月"
from (select a.emplid,
a.pmonth,
a.pyear,
sum(a.salary) salary,
DENSE_RANK() OVER(partition by pyear,pmonth order by sum(a.salary) desc) mr1
from TB_PERFORMANCE a
group by a.emplid,a.pyear, pmonth) t
where emplid = 1001 group by emplid,pyear;

0
5 收藏

0 评论
5 收藏
0