MySQL SUM() 基于与 SELECT 不同的条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7655219/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SUM() based on a different condition to the SELECT
提问by user970117
Hi is there a way that I can do the SUM(total_points) based on a different condition to the rest of the SELECT statement, so I want the SUM(total_points) for every row which is <= to $chosentrack? but the rest of the conditions of the SELECT statement to be what they are below. I need them to all be returned together..as I am populating a league table.
嗨，有没有一种方法可以根据与 SELECT 语句的其余部分不同的条件来计算 SUM(total_points)，所以我想要 <= 到 $chosentrack 的每一行的 SUM(total_points)？但是 SELECT 语句的其余条件是它们下面的内容。我需要将它们全部归还……因为我正在填写排行榜。
Thanks a lot for any help.
非常感谢您的帮助。
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
回答by Charles Bretana
You can also put the sum inside a case statement, where the case evaluates the other condition, and then only sum thoses records where the condition is true...
您还可以将总和放在 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
回答by a1ex07
SELECT ...
SUM(CASE
WHEN track_id <= [your_value] THEN total_points
ELSE 0
END
) AS total_points, ....
回答by Icarus
Do the sum as a subselect, making sure that you select an additional column (don't forget to group by that column) and join the result of this subselect to the main query using the common field.
作为子选择进行求和，确保您选择了一个附加列（不要忘记按该列分组）并使用公共字段将此子选择的结果连接到主查询。
Template:
模板：
Select col1,
col2 as t,x.thesum
From table t left join
( select sum(colx) as thesum, col1 from t where ...
Group by col1) x on t.col1=x.col1
Where ....
