回答編集履歴
1
ちょうせい
answer
CHANGED
|
@@ -1,5 +1,4 @@
|
|
|
1
|
-
|
|
1
|
+
調整しました
|
|
2
|
-
|
|
3
2
|
```SQL
|
|
4
3
|
create table tbl(id int primary key,k int,nameA varchar(20),nameB varchar(20));
|
|
5
4
|
|
|
@@ -13,10 +12,6 @@
|
|
|
13
12
|
(7,4,'n41','n411'),
|
|
14
13
|
(8,4,'n42','n422'),
|
|
15
14
|
(9,5,'n51','n511');
|
|
16
|
-
|
|
17
|
-
create view v as
|
|
18
|
-
select *,(select count(*)+1 from tbl where id<t1.id and k=t1.k) as rank
|
|
19
|
-
from tbl as t1;
|
|
20
15
|
```
|
|
21
16
|
|
|
22
17
|
結果
|
|
@@ -28,6 +23,8 @@
|
|
|
28
23
|
max(case when rank=2 then nameB else null end) as nb2,
|
|
29
24
|
max(case when rank=3 then nameA else null end) as na3,
|
|
30
25
|
max(case when rank=3 then nameB else null end) as nb3
|
|
31
|
-
from
|
|
26
|
+
from (
|
|
27
|
+
select *,(select count(*)+1 from tbl where id<t1.id and k=t1.k) as rank
|
|
28
|
+
from tbl as t1) as t2
|
|
32
|
-
group by k
|
|
29
|
+
group by k
|
|
33
30
|
```
|