行列转换问题

Mozhx 2014-01-21 11:04:31

我要实现如图的查询结果，就是查询每个人的num列值最大的前四条记录，然后每人只显示一条记录，如何实现

create table mytable (
mytableid char(36) not null,
pid char(36),
num number(6),
startdate date,
enddate date,
sumgrade number(6) default 0,
constraint PK_RCCYCLE primary key (mytableid)
);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('1', '1 ', 1, to_date('14-01-2013', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'), 0);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('2', '1 ', 2, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-07-2013', 'dd-mm-yyyy'), -1);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('3', '1 ', 3, to_date('01-08-2013', 'dd-mm-yyyy'), to_date('31-10-2013', 'dd-mm-yyyy'), 0);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('4', '1 ', 4, to_date('01-11-2013', 'dd-mm-yyyy'), to_date('31-01-2014', 'dd-mm-yyyy'), 0);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('5', '1 ', 5, to_date('01-02-2014', 'dd-mm-yyyy'), to_date('30-04-2014', 'dd-mm-yyyy'), -2);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('6', '1 ', 6, to_date('01-05-2014', 'dd-mm-yyyy'), to_date('31-07-2014', 'dd-mm-yyyy'), 0);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('7', '1 ', 7, to_date('01-08-2014', 'dd-mm-yyyy'), to_date('31-10-2014', 'dd-mm-yyyy'), 0);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('8', '1 ', 8, to_date('01-11-2014', 'dd-mm-yyyy'), to_date('14-01-2015', 'dd-mm-yyyy'), 0);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('9', '2 ', 1, to_date('14-01-2014', 'dd-mm-yyyy'), to_date('30-04-2014', 'dd-mm-yyyy'), 0);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('10', '2 ', 1, to_date('14-01-2014', 'dd-mm-yyyy'), to_date('30-04-2014', 'dd-mm-yyyy'), 0);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('11', '2 ', 2, to_date('01-05-2014', 'dd-mm-yyyy'), to_date('31-07-2014', 'dd-mm-yyyy'), 0);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('12', '2 ', 2, to_date('01-05-2014', 'dd-mm-yyyy'), to_date('31-07-2014', 'dd-mm-yyyy'), 0);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('13', '2 ', 3, to_date('01-08-2014', 'dd-mm-yyyy'), to_date('31-10-2014', 'dd-mm-yyyy'), -3);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('14', '2 ', 3, to_date('01-08-2014', 'dd-mm-yyyy'), to_date('31-10-2014', 'dd-mm-yyyy'), 0);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('15', '2 ', 4, to_date('01-11-2014', 'dd-mm-yyyy'), to_date('31-01-2015', 'dd-mm-yyyy'), 0);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('16', '2 ', 4, to_date('01-11-2014', 'dd-mm-yyyy'), to_date('14-01-2015', 'dd-mm-yyyy'), 0);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('17', '2 ', 5, to_date('01-02-2015', 'dd-mm-yyyy'), to_date('30-04-2015', 'dd-mm-yyyy'), 0);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('18', '2 ', 6, to_date('01-05-2015', 'dd-mm-yyyy'), to_date('14-05-2015', 'dd-mm-yyyy'), 0);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('19', '3 ', 1, to_date('14-01-2014', 'dd-mm-yyyy'), to_date('30-04-2014', 'dd-mm-yyyy'), -10);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('20', '3 ', 2, to_date('01-05-2014', 'dd-mm-yyyy'), to_date('14-07-2014', 'dd-mm-yyyy'), -5);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('21', '5', 1, to_date('14-10-2013', 'dd-mm-yyyy'), to_date('31-01-2014', 'dd-mm-yyyy'), -8);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('22', '5', 2, to_date('01-02-2014', 'dd-mm-yyyy'), to_date('30-04-2014', 'dd-mm-yyyy'), -2);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('23', '5', 3, to_date('01-05-2014', 'dd-mm-yyyy'), to_date('31-07-2014', 'dd-mm-yyyy'), -1);

insert into mytable (mytableID, pid, NUM, STARTDATE, ENDDATE, SUMGRADE)
values ('24', '5', 4, to_date('01-08-2014', 'dd-mm-yyyy'), to_date('14-10-2014', 'dd-mm-yyyy'), null);

...全文

168 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

snbxp 2014-01-23

打赏
举报


 WITH TB AS
  (SELECT PID,
          NUM,
          SUMGRADE,
          ROW_NUMBER() OVER(PARTITION BY PID ORDER BY NUM DESC) RN
     FROM MYTABLE)
 SELECT 
     pid,
     max(DECODE(rn,1,NUM,NULL)) num1,
     max(DECODE(rn,1,SUMGRADE,NULL)) SUMGRADE1,
     max(DECODE(rn,2,NUM,NULL)) num2,
     max(DECODE(rn,2,SUMGRADE,NULL)) SUMGRADE2,
     max(DECODE(rn,3,NUM,NULL)) num3,
     max(DECODE(rn,3,SUMGRADE,NULL)) SUMGRADE3,
     max(DECODE(rn,4,NUM,NULL)) num4,
     max(DECODE(rn,4,SUMGRADE,NULL)) SUMGRADE4
 FROM  TB
 GROUP BY pid

snbxp 2014-01-22

打赏
举报


 WITH TB AS
  (SELECT PID,
          NUM,
          SUMGRADE,
          ROW_NUMBER() OVER(PARTITION BY PID ORDER BY NUM DESC) RN
     FROM MYTABLE)
 SELECT 
     pid,
     max(DECODE(rn,1,NUM,NULL)) num1,
     max(DECODE(rn,1,SUMGRADE,NULL)) SUMGRADE1,
     max(DECODE(rn,2,NUM,NULL)) num2,
     max(DECODE(rn,2,SUMGRADE,NULL)) SUMGRADE2,
     max(DECODE(rn,3,NUM,NULL)) num3,
     max(DECODE(rn,2,SUMGRADE,NULL)) SUMGRADE3,
     max(DECODE(rn,4,NUM,NULL)) num4,
     max(DECODE(rn,2,SUMGRADE,NULL)) SUMGRADE4
 FROM  TB
 GROUP BY pid