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