我正在使用 codeigniter-4.1.9。我有一個分數表，并且有多個回合。每個到達那里的用戶在不同的回合中得分。我想用不同的輪次在一行中檢索每個用戶的分數，但無法獲得預期的結果。如何解決這個問題？
我都試過了：
$scmodel->query("SELECT DISTINCT * FROM score GROUP BY score");
要么
$scmodel->query("SELECT DISTINCT * FROM score GROUP BY teamid");
預期結果應該：
teamid round1 round2 round3
1 20 30 40
2 50 30 90
3 45 38 70
但是得到：
teamid round1 round2 round3
1 20
2 50
3 45
1 30
2 30
3 38
1 40
2 90
3 70
分數表是
uj5u.com熱心網友回復：
如果您希望將回合作為單獨的列，則需要進行pivot query如下操作：
$scmodel->query("
SELECT teamid,
SUM(IF(roundid = 1, score, 0)) as round1,
SUM(IF(roundid = 2, score, 0)) as round2,
SUM(IF(roundid = 3, score, 0)) as round3
FROM score
GROUP BY teamid
");
樣本資料：
create table score (id integer(3),
teamid varchar(100),
jid integer(3),
roundid integer(3),
challengeid integer(3),
score integer(5),
mailstatus integer(3)
);
insert into score values
(1, 'CTO202241400-GOLD', 2, 2, 4, 89, 1),
(2, 'CTO20220171-DIAM', 2, 1, 3, 75, 1),
(3, 'CTO-2022-1133224-ZOOM', 2, 1, 4, 87, 1),
(4, 'CTO20221888-EAGL', 2, 2, 4, 76, 1),
(5, 'CTO2022889-ABGR', 2, 3, 5, 59, 1),
(6, 'CTO202241400-GOLD', 2, 3, 5, 89, 1),
(7, 'CTO20221888-EAGL', 2, 3, 5, 97, 1),
(8, 'CTO-2022-1133224-ZOOM', 2, 3, 5, 67, 1),
(9, 'CTO20220171-DIAM', 2, 3, 5, 98, 1);
輸出：
| teamid | round1 | round2 | round3 |
|-----------------------|--------|--------|--------|
| CTO-2022-1133224-ZOOM | 87 | 0 | 67 |
| CTO20220171-DIAM | 75 | 0 | 98 |
| CTO20221888-EAGL | 0 | 76 | 97 |
| CTO202241400-GOLD | 0 | 89 | 89 |
| CTO2022889-ABGR | 0 | 0 | 59 |
SQL小提琴：http ://www.sqlfiddle.com/#!9/7ed3fa/3/0
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/453536.html