回答編集履歴
1
sample
answer
CHANGED
|
@@ -1,1 +1,33 @@
|
|
|
1
|
-
できないことは無いですが行番号を最初から調整しないと集計列があわないですよね
|
|
1
|
+
できないことは無いですが行番号を最初から調整しないと集計列があわないですよね
|
|
2
|
+
|
|
3
|
+
# sample
|
|
4
|
+
postgreだと動かないかもしれませんが一応sample
|
|
5
|
+
|
|
6
|
+
|
|
7
|
+
- 元データ
|
|
8
|
+
```SQL
|
|
9
|
+
create table user (uid int unique,uname varchar(20),gender varchar(10),birth date);
|
|
10
|
+
insert into user values
|
|
11
|
+
(1,'山田','男','1990/01/01'),
|
|
12
|
+
(2,'鈴木','女','1992/03/31'),
|
|
13
|
+
(3,'高橋','男','1980/02/21');
|
|
14
|
+
|
|
15
|
+
create table gakureki (uid int,row int,gname varchar(20),gakka varchar(20),sdate date,edate date);
|
|
16
|
+
insert into gakureki values
|
|
17
|
+
(1,1,'A高校','普通科','2005/04/01','2008/03/31'),
|
|
18
|
+
(1,2,'B大学','経済学部経済学科','2009/04/01','2013/03/31'),
|
|
19
|
+
(1,3,'B大学院','経営マネジメント学科','2013/04/01','2015/03/31'),
|
|
20
|
+
(2,1,'Z高校','普通科','2008/04/01','2011/03/31'),
|
|
21
|
+
(2,2,'X大学','法学部法律学科','2011/04/01','2015/03/31'),
|
|
22
|
+
(3,1,'M高校','普通科','1995/04/01','1998/03/31'),
|
|
23
|
+
(3,2,'N大学','外国語学部英語学科','1998/04/01','2002/03/31');
|
|
24
|
+
```
|
|
25
|
+
|
|
26
|
+
- 集計
|
|
27
|
+
```SQL
|
|
28
|
+
select * from
|
|
29
|
+
(select uid,(select max(row)-t1.row+1 from gakureki where t1.uid=uid) as rank,gname,gakka,sdate,edate from gakureki as t1 having rank=1) as t1
|
|
30
|
+
left join (select uid,(select max(row)-t1.row+1 from gakureki where t1.uid=uid) as rank,gname,gakka,sdate,edate from gakureki as t1 having rank=2) as t2 on t1.uid=t2.uid
|
|
31
|
+
left join (select uid,(select max(row)-t1.row+1 from gakureki where t1.uid=uid) as rank,gname,gakka,sdate,edate from gakureki as t1 having rank=3) as t3 on t1.uid=t3.uid
|
|
32
|
+
|
|
33
|
+
```
|