mysql分组group by多条件_在具有多个条件的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 |

+------------------------+----------------+----+---------+---+