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

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

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网