下面是需求
表名：tb_performance主要字段：pyear、pmonth、salary、emplId 1. 条件：年份、员工编号 结果：统计该员工每个月的绩效工资排名 要求展示月份、绩效工资、排名 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) mr1from TB_PERFORMANCE awhere pyear = 2013group by a.emplid, pmonth) twhere 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) mr1from TB_PERFORMANCE agroup by a.emplid,a.pyear, pmonth) twhere emplid = 1001 group by emplid,pyear;