gpt4 book ai didi

php - 对mysql中同一个表的多列求和

转载 作者:行者123 更新时间:2023-11-29 07:30:37 25 4
gpt4 key购买 nike

我正在开发体育网站。

在此站点中,我有两个表,一个是 tbl_team,用于存储球队详细信息,另一个表是 tbl_tournamentmatches,用于存储不同轮次锦标赛比赛的详细信息。

在我的tbl_tournamentmatches中,我存储team1_idteam2_idteam1_scoreteam2_score.

我的表有这样的条目:

tbl_tournamentmatches

match_id team1_id team2_id team1_score team2_score
5 4 9 15 5
6 9 16 15 5
7 4 16 5 15
8 4 16 5 15

tbl_team

team_id team_title
4 KKR
9 RR
16 CSK

我想要的结果应该是这样的:-

Team name Score
CSK 35
KKR 25
RR 20

我使用了这个查询:-

select * from 
(
SELECT sum(team1_points) as totalpoints,t.team_title
from tbl_team t
left join tbl_tournamentmatches m
on t.team_id = m.team1_id
where tournament_id = 3 AND agegroup_id = 36
group by team1_id
union
SELECT sum(team2_points) as totalpoints,t.team_title
from tbl_team t
left join tbl_tournamentmatches m
on t.team_id = m.team2_id
where tournament_id = 3 AND agegroup_id = 36
group by team2_id
) s

但是我得到了这样的结果:-

KKR   25
RR 15
RR 5
CSK 35

任何帮助将不胜感激。提前致谢。

最佳答案

进行连接以获得每场比赛的球队和分数,然后对结果进行求和:-

SELECT team_title, sum(team_points) as totalpoints 
FROM
(
SELECT team1_id AS team_id, team1_points AS team_points, t.team_title
FROM tbl_team t
LEFT JOIN tbl_tournamentmatches m
ON t.team_id = m.team1_id
WHERE tournament_id = 3 AND agegroup_id = 36
UNION ALL
SELECT team2_id AS team_id, team2_points AS team_points, t.team_title
FROM tbl_team t
LEFT JOIN tbl_tournamentmatches m
ON t.team_id = m.team2_id
WHERE tournament_id = 3 AND agegroup_id = 36
) s
GROUP BY team_id, team_title

关于php - 对mysql中同一个表的多列求和,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/32758354/

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