php – 查找点和分组的总和

我的桌子

参考：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

如果有人能帮助我,我将不胜感激, 最佳答案 当然,你可以做一些优化.但它的确有效！

SELECT 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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!