使用嵌套select子式解决mysql不能叠加使用如max(sum())的问题

网上也有解决方案 有的有瑕疵 有的较复杂（mysql没有分析函数，可以使用变量实现） s

elect sumScoreValue,studentid,studentName from sc_studentb, ( select sum (scoreValue) as sumScoreValue,studentid from sc_score group by studentid order by sumSc

网上也有解决方案 有的有瑕疵 有的较复杂（mysql没有分析函数，可以使用变量实现）

select sumScoreValue,studentid,studentName from sc_student b, 
(select sum(scoreValue) as sumScoreValue, studentid 
from sc_score group by studentid 
order by sumScoreValue desc limit 1) as a 
where a.studentid=b.studentNo 
//这样做 只能查询第一名只有一个的情况 很巧妙
mysql> select studentid,scoreValue from sc_score; 
+-----------+------------+ 
| studentid | scoreValue | 
+-----------+------------+ 
| 1 | 80 | 
| 1 | 85 | 
| 1 | 90 | 
| 2 | 75 | 
| 2 | 80 | 
| 2 | 84 | 
| 3 | 85 | 
| 3 | 85 | 
| 3 | 85 | 
+-----------+------------+ 
9 rows in set (0.00 sec) 
 
mysql> SELECT studentNo,studentName FROM sc_student; 
+-----------+-------------+ 
| studentNo | studentName | 
+-----------+-------------+ 
| 1 | aa | 
| 2 | bb | 
| 3 | cc | 
+-----------+-------------+ 
3 rows in set (0.00 sec) 
 
 
mysql> SELECT a.studentid, 
 -> b.studentName, 
 -> a.sumScoreValue 
 -> FROM (SELECT tmp.studentid, 
 -> tmp.sumScoreValue, 
 -> IF(@groupid = tmp.sumScoreValue,@rank := 1,@rank := @rank + 1) AS rank, 
 -> @groupid := tmp.sumScoreValue 
 -> FROM (SELECT studentid, 
 -> SUM(scoreValue) AS sumScoreValue 
 -> FROM sc_score 
 -> GROUP BY studentid 
 -> ORDER BY scoreValue DESC) tmp, 
 -> (SELECT @rank := 0,@groupid := '') m) a, 
 -> sc_student b 
 -> WHERE a.studentid = b.studentNo 
 -> AND a.rank = 1; 
+-----------+-------------+---------------+ 
| studentid | studentName | sumScoreValue | 
+-----------+-------------+---------------+ 
| 3 | cc | 255 | 
| 1 | aa | 255 | 
+-----------+-------------+---------------+ 
2 rows in set (0.00 sec)

这是使用变量做的

自己重新做了

select s.id,s.stuid,stu.stuname, sumscore
from score s left join student stu on s.stuid = stu.stuid left join (select s.id,s.stuid,stu.stuname,sum(s.score) as sumscore
from score s left join student stu on s.stuid = stu.stuid where s.gradeid=4 and s.classid=1 and s.season=1 group by s.stuid) as t1 on t1.id=s.id where s.gradeid=4 and s.classid=1 and s.season=1 and sumscore in(select max(sumscore) from (select s.id,s.stuid,stu.stuname,sum(s.score) as sumscore
from score s left join student stu on s.stuid = stu.stuid where s.gradeid=4 and s.classid=1 and s.season=1 group by s.stuid) as t2)