在具有多个前提的MySQL中行使group by子句的正确要领是什么？

我正在编写一个查询,假如每个专业的均匀后果均高出80,它将找到每个专业中年数最小的门生,并按照以下相关按他们的名字排序.我正在行使MySQL处事器并正在行使MySQL Workbench.

门生：

snum: integer
name: string
major: string
level: string
age: integer

类：

cname: string
meets_at: time
room: string
fid: integer

年级：

snum (foreign key)
name (foreign key)
score 

这是我实行实现查询的方法.

select S.major,S.name,S.age
from student S,grades G
group by S.major
Having MIN(S.age) and G.score > (Select avg(G.score) 
                                from grades G1,student S
                                where S.snum = G1.snum) ;

可是,这行不通,我对查询的外面感想很是狐疑.

样本数据：

CREATE TABLE students
(`snum` int,`name` varchar(18),`major` varchar(22),`standing` varchar(2),`age` int)
 ;

INSERT INTO student
(`snum`,`name`,`major`,`standing`,`age`)
VALUES
(578875478,'Edward Baker','Veterinary Medicine','SR',21),(574489456,'Betty Adams','Economics','JR',20),(573284895,'Steven Green','Kinesiology','SO',19),(567354612,'Karen Scott','Computer Engineering','FR',18),(556784565,'Kenneth Hill','Civil Engineering',(552455318,'Ana Lopez',(550156548,'George Wright','Education',(462156489,'Donald King','Mechanical Engineering',(455798411,'Luis Hernandez','Electrical Engineering',17),(451519864,'Mark Young','Finance',(351565322,'Nancy Allen','Accounting',(348121549,'Paul Hall','Computer Science',(322654189,'Lisa Walker',(320874981,'Daniel Lee',(318548912,'Dorthy Lewis',(301221823,'Juan Rodriguez','Psychology',(280158572,'Margaret Clark','Animal Science',(269734834,'Thomas Robinson',(132977562,'Angela Martinez','History',(115987938,'Christopher Garcia',(112348546,'Joseph Thompson',(99354543,'Susan Martin','Law',(60839453,'Charles Harris','Architecture',22),(51135593,'Maria White','English',21);

CREATE TABLE grades
(`snum` int,`cname` varchar(23),`score` int);

INSERT INTO grades
(`snum`,`cname`,`score`)
VALUES
(574489456,'Urban Economics',45),'Operating System Design',98),'Data Structures',100),'Communication Networks',87),'Optical Electronics','Database Systems',90),97),56),'Perception','Social Cognition',80),35),'Patent Law',65)
 ;

预期后果：

+------------------------+----------------+----+---------+---+
| Computer Engineering   | Karen Scott    | 18 | 99.0000 | 1 |
+------------------------+----------------+----+---------+---+
| Computer Science       | Paul Hall      | 18 | 90.0000 | 1 |
+------------------------+----------------+----+---------+---+
| Electrical Engineering | Luis Hernandez | 17 | 93.5000 | 1 |
+------------------------+----------------+----+---------+---+
| Psychology             | Juan Rodriguez | 20 | 87.0000 | 1 |
+------------------------+----------------+----+---------+---+

起首,您可以行使简朴的汇总查询来计较每个门生的均匀分数：

SELECT s.major,s.name,s.age,AVG(g.score) avg_score
FROM 
    students s
    INNER JOIN grades g ON g.snum = s.snum
GROUP BY s.snum,s.major,s.age
HAVING AVG(g.score) > 80

这将为每位均匀得分高于80的门生及其年数,姓名和专业以及均匀得分提供一笔记录.

此刻剩下要做的就是在每组具有沟通专业的门生中选择最年青的门生.这可以通过窗口函数ROW_NUMBER()完成：

SELECT major,name,age,avg_score
FROM (
    SELECT
        x.*,ROW_NUMBER() OVER(PARTITION BY major ORDER BY age) rn
    FROM (
        SELECT s.major,AVG(g.score) avg_score
        FROM 
            students s
            INNER JOIN grades g ON g.snum = s.snum
        GROUP BY s.snum,s.age
        HAVING AVG(g.score) > 80
    ) x
) z WHERE rn = 1

带有示例数据的DB Fiddle返回：

| major                  | name           | age | avg_score |
| ---------------------- | -------------- | --- | --------- |
| Computer Engineering   | Karen Scott    | 18  | 99        |
| Computer Science       | Paul Hall      | 18  | 90        |
| Electrical Engineering | Luis Hernandez | 17  | 93.5      |
| Psychology             | Juan Rodriguez | 20  | 87        |

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!