gpt4 book ai didi

mysql - SUM() 基于与 SELECT 不同的条件

转载 作者:IT王子 更新时间:2023-10-28 23:53:41 27 4
gpt4 key购买 nike

嗨,有没有一种方法可以根据与 SELECT 语句的其余部分不同的条件来执行 SUM(total_points),所以我想要 <= 到 $chosentrack 的每一行的 SUM(total_points)?但 SELECT 语句的其余条件如下。我需要将它们全部归还..因为我正在填充排行榜。

非常感谢您的帮助。

SELECT
members.member_id,
members.teamname,
SUM(total_points) as total_points,
total_points as last_race_points
FROM
members,
members_leagues,
member_results
WHERE
members.member_id = members_leagues.member_id
AND members_leagues.league_id = '$chosenleague'
AND member_results.track_id = '$chosentrack'
AND members_leagues.start_race = '$chosentrack'
AND member_results.member_id = members_leagues.member_id
GROUP BY
members.member_id
ORDER BY
member_results.total_points DESC,
last_race_points DESC,
members.teamname DESC

最佳答案

您还可以将总和放在 case 语句中,其中 case 评估其他条件,然后只对条件为真的那些记录求和......

  SELECT m.member_id, m.teamname, 
Sum(Case When r.track_Id = '$chosentrack'
Then total_points Else 0 End) TotalChosenTrackPoints,
Sum(Case When r.track_Id < '$chosentrack'
Then total_points Else 0 End) TotalLessThanChosenTrackPoints,
total_points as last_race_points
FROM members m
Join members_leagues l
On l.member_id = m.member_id
Join member_results r
On r.member_id = m.member_id
Where l.league_id = '$chosenleague'
And l.start_race = '$chosentrack'
Group By m.member_id
Order By r.total_points Desc,
last_race_points Desc, m.TeamName Desc

关于mysql - SUM() 基于与 SELECT 不同的条件,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/7655219/

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