Oracle
行列转换问题
墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网
我也要 关注 + 加入社区
我要实现如图的查询结果，就是查询每个人的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);
...全文
