下面是需求
表名：tb_performance主要字段：pyear、pmonth、salary、emplId1.
条件：年份、员工编号
结果：统计该员工每个月的绩效工资排名
要求展示月份、绩效工资、排名
2.
条件：员工编号
结果：统计该员工每年每月的绩效工资排名
要求展示年度、月份、绩效工资、排名，横向展示12个月绩效工资及排名
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;
