回答編集履歴
1
参考
test
CHANGED
|
@@ -2,3 +2,37 @@
|
|
|
2
2
|
最終的なデータ表示も横方向に持たせるのはRDB的ではありませんが
|
|
3
3
|
縦に表示してピボットすればなんとか
|
|
4
4
|
ただし同順位や３位が複数名いて溢れたときにどうするなど情報不足があります
|
|
5
|
+
|
|
6
|
+
# 参考
|
|
7
|
+
構造的にはこうしてください
|
|
8
|
+
```SQL
|
|
9
|
+
create table tbl(ID int primary key auto_increment,d date,user varchar(20),score int,unique(d,user));
|
|
10
|
+
insert into tbl(d,user,score) values
|
|
11
|
+
('2022-01-01','A',30),
|
|
12
|
+
('2022-01-01','B',40),
|
|
13
|
+
('2022-01-01','C',50),
|
|
14
|
+
('2022-01-01','D',10),
|
|
15
|
+
('2022-01-01','E',20),
|
|
16
|
+
('2022-01-02','A',40),
|
|
17
|
+
('2022-01-02','B',45),
|
|
18
|
+
('2022-01-02','C',55),
|
|
19
|
+
('2022-01-02','D',15),
|
|
20
|
+
('2022-01-03','A',45),
|
|
21
|
+
('2022-01-03','B',40),
|
|
22
|
+
('2022-01-03','C',50),
|
|
23
|
+
('2022-01-03','D',20),
|
|
24
|
+
('2022-01-03','E',15);
|
|
25
|
+
|
|
26
|
+
select d
|
|
27
|
+
,group_concat(case user when 'A' then score else null end) as A
|
|
28
|
+
,group_concat(case user when 'B' then score else null end) as B
|
|
29
|
+
,group_concat(case user when 'C' then score else null end) as C
|
|
30
|
+
,group_concat(case user when 'D' then score else null end) as D
|
|
31
|
+
,group_concat(case user when 'E' then score else null end) as E
|
|
32
|
+
from tbl
|
|
33
|
+
group by d;
|
|
34
|
+
|
|
35
|
+
select date_format(d,'%Y-%m')as ym,user,sum(score) as sum from tbl
|
|
36
|
+
group by ym,user
|
|
37
|
+
order by sum desc
|
|
38
|
+
```
|