SUM（）基于与SELECT不同的条件

程序猿视觉

嗨，有一种方法可以根据与SELECT语句其余部分不同的条件来执行SUM（total_points），所以我希望每行
非常感谢您的帮助。
  P$ S( ?9 Z; F1 `1 pSELECT
    members.member_id,
    members.teamname,
    SUM(total_points) as total_points,
    total_points as last_race_points
* R1 T7 B" N; O  T! nFROM
" b9 n* E  ?  \+ [, m& t! x    members,
    members_leagues,
7 ?: ?5 ?/ B" J2 p4 \+ v7 r' o    member_results
WHERE
; m( K* i1 S- E- K4 J2 S% N8 p    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'
$ g, ~; `& N2 \    AND member_results.member_id = members_leagues.member_id
GROUP BY
: Q! e1 ?( f* {: C2 U4 F    members.member_id
3 A5 z7 Q: W; x: j( P6 L: iORDER BY
2 m0 [# L- ]% S0 R    member_results.total_points DESC,
9 [: O' u9 D# {; K; {" B* [: k4 e    last_race_points DESC,
    members.teamname DESC
               
解决方案:
               


                您也可以将总和放在case语句中，在case语句中评估其他条件，然后仅对条件为true的那些记录求和…
! |7 K( C" J2 W. K& N  SELECT m.member_id, m.teamname,
    Sum(Case When r.track_Id = '$chosentrack'
         Then total_points Else 0 End) TotalChosenTrackPoints,
! z* Y7 V! y! D/ g0 q# U( R6 g# k    Sum(Case When r.track_Id < '$chosentrack'
  d" _2 W5 N6 J' m7 m! l         Then total_points Else 0 End) TotalLessThanChosenTrackPoints,
' P' V1 v! Z1 r0 k% e' d+ U    total_points as last_race_points  
FROM members m
: x2 S( Z) o/ i% p9 @' w    Join members_leagues l
1 _5 U1 P8 }1 Q% B' [: |       On l.member_id = m.member_id  
    Join member_results r
       On r.member_id = m.member_id
/ q* p* ~3 m& G& Z Where l.league_id = '$chosenleague'
    And l.start_race = '$chosentrack'
$ k6 T5 h, x9 u3 t0 a Group By m.member_id
Order By r.total_points Desc,
     last_race_points  Desc, m.TeamName Desc