网上也有解决方案 有的有瑕疵 有的较复杂（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)
