gpt4 book ai didi

MYSQL/查询 : How to make table rows into column

转载 作者:行者123 更新时间:2023-11-29 02:48:03 26 4
gpt4 key购买 nike

我有 3 个表 tbl_contestant、tbl_criteria 和 tbl_judges。然后我将另外 1 个表组合这 3 个表作为我的结果,tbl_score。

tbl_criteria
------------------------
crit_id | criteria_name
16 | sports

tbl_judges
------------------------
judge_id | judge_name
61 | first
62 | second
63 | third


tbl_cotestant
--------------------------------------
con_id | contestant_number | contestant_name |
1 | 1 | john |
2 | 2 | sy |
3 | 3 | Nah |


tbl_score
--------------------------------------------------
score_id | crit_id | judge_id | contestant_number | score
1 | 16 | 61 | 1 | 25
2 | 16 | 61 | 2 | 25
3 | 16 | 61 | 3 | 25
4 | 16 | 62 | 1 | 25
5 | 16 | 62 | 2 | 73
6 | 16 | 62 | 3 | 59
7 | 16 | 63 | 1 | 70
8 | 16 | 63 | 2 | 80
9 | 16 | 63 | 3 | 70

如何实现这个输出,judge_id行根据crit_id变成列

contestant_number | contestant_name | 16_judge_61 | 16_judge_62 | 16_judge_63 | total
1 | john | 25 | 25 | 70 |
2 | sy | 25 | 73 | 80 |
3 | Nah | 25 | 59 | 70 |

请更正我的问题

SELECT DISTINCT(c.contestant_number) , contestant_name , j1.sports as 
16_judge_61, j2.sports as 16_judge_62, j3.sports as 16_judge_63 from
tbl_criteria , tbl_score, tbl_contestant c
LEFT JOIN tbl_ // <-- i have no idea how start from here joining those 4 tables together

最佳答案

您可以使用 CASE WHEN 来解决这个问题。

SELECT 
s.contestant_number,
c.contestant_name,
SUM(CASE WHEN s.crit_id='16' AND s.judge_id='61' THEN s.score END) as 16_judge_61,
SUM(CASE WHEN s.crit_id='16' AND s.judge_id='62' THEN s.score END) as 16_judge_62,
SUM(CASE WHEN s.crit_id='16' AND s.judge_id='63' THEN s.score END) as 16_judge_63,
SUM(s.score) as Total
FROM tbl_score s
INNER JOIN tbl_contestant c ON s.contestant_number = c.contestant_number
GROUP BY s.contestant_number

参见 SQL Fiddle http://sqlfiddle.com/#!9/9efa5/1

关于MYSQL/查询 : How to make table rows into column,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/39434767/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com