人物设计网站,盐田做网站的公司,东莞展馆设计公司,电脑培训班零基础网上也有解决方案 有的有瑕疵 有的较复杂(mysql没有分析函数#xff0c;可以使用变量实现) select 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_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, studentidfrom sc_score group by studentidorder by sumScoreValue desc limit 1) as awhere a.studentidb.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, sumscorefrom score s left join student stu on s.stuid stu.stuid left join (select s.id,s.stuid,stu.stuname,sum(s.score) as sumscorefrom score s left join student stu on s.stuid stu.stuid where s.gradeid4 and s.classid1 and s.season1 group by s.stuid) as t1 on t1.ids.id where s.gradeid4 and s.classid1 and s.season1 and sumscore in(select max(sumscore) from (select s.id,s.stuid,stu.stuname,sum(s.score) as sumscorefrom score s left join student stu on s.stuid stu.stuid where s.gradeid4 and s.classid1 and s.season1 group by s.stuid) as t2)
