使用嵌套select子式 解决mysql不能叠加使用如max(sum())的问题
程序员文章站
2022-06-02 20:20:42
...
网上也有解决方案 有的有瑕疵 有的较复杂(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)