Home > Database > Mysql Tutorial > 使用嵌套select子式 解决mysql不能叠加使用如max(sum())的问题

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

不言
Release: 2018-05-16 16:52:49
Original
4043 people have browsed it

网上也有解决方案 有的有瑕疵 有的较复杂(mysql没有分析函数,可以使用变量实现) s

elect sumScoreValue,studentid,studentName from sc_studentb, ( select sum (scoreValue) as sumScoreValue,studentid from sc_score group by studentid order by sumSc
Copy after login

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

这是使用变量做的

自己重新做了

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)
Copy after login


Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template