teratail header banner
teratail header banner
質問するログイン新規登録

回答編集履歴

1

ちょうせい

2021/09/01 05:32

投稿

yambejp
yambejp

スコア117944

answer CHANGED
@@ -1,5 +1,4 @@
1
- 最初からrank付用のカラムをつくっておくか、viewで対応すると楽です
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 v
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
  ```