我的桌子
参考：http://www.sqlfiddle.com/#!2/6be93/1
在这里,我想找到一所学校的总分.我正在使用以下代码.
SELECT School,SUM(GroupPoint) AS TotalC1,SUM(C2Points) AS TotalC2,SUM(C3Points) AS TotalC3,SUM(GroupPoint + C2Points + C3Points) AS TotalAll
FROM students GROUP BY School ORDER BY TotalAll DESC LIMIT 6
参考：http://www.sqlfiddle.com/#!2/25ed3/2
我的问题,身份证1,2,3是团体比赛的获胜者.所以他们分别得到5分.但是对于那场比赛,学校只会获得5分而不是15分.一组可以由同一个ChessNO识别.
所以我的最终输出看起来
SCHOOL TOTALC1 TOTALC2 TOTALC3 TOTALALL
School2 13 49 3 65
School1 5 4 25 34
如果有人能帮助我,我将不胜感激,
最佳答案
当然,你可以做一些优化.但它的确有效！
原文链接：https://www.f2er.com/mysql/433164.htmlSELECT two.TOTALC1,one.TotalC2,one.TotalC3,one.TotalOne + two.TOTALC1 as TotalAll from
( select School,SUM(C2Points + C3Points) AS TotalOne
FROM students GROUP BY School
ORDER BY TotalOne DESC) one
left join (select school,sum(ma) as TOTALC1 from (select school,chess,max(grouppoint) as ma from students group by school,chess) as b group by school) two
on one.school = two.school