mysql - 在具有多个条件的 MySQL 中使用 group by 子句的正确方法是什么？

Question

我正在编写一个查询，如果他们的平均成绩超过 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', 'SR', 21),
(552455318, 'Ana Lopez', 'Computer Engineering', 'SR', 19),
(550156548, 'George Wright', 'Education', 'SR', 21),
(462156489, 'Donald King', 'Mechanical Engineering', 'SO', 19),
(455798411, 'Luis Hernandez', 'Electrical Engineering', 'FR', 17),
(451519864, 'Mark Young', 'Finance', 'FR', 18),
(351565322, 'Nancy Allen', 'Accounting', 'JR', 19),
(348121549, 'Paul Hall', 'Computer Science', 'JR', 18),
(322654189, 'Lisa Walker', 'Computer Science', 'SO', 17),
(320874981, 'Daniel Lee', 'Electrical Engineering', 'FR', 17),
(318548912, 'Dorthy Lewis', 'Finance', 'FR', 18),
(301221823, 'Juan Rodriguez', 'Psychology', 'JR', 20),
(280158572, 'Margaret Clark', 'Animal Science', 'FR', 18),
(269734834, 'Thomas Robinson', 'Psychology', 'SO', 18),
(132977562, 'Angela Martinez', 'History', 'SR', 20),
(115987938, 'Christopher Garcia', 'Computer Science', 'JR', 20),
(112348546, 'Joseph Thompson', 'Computer Science', 'SO', 19),
(99354543, 'Susan Martin', 'Law', 'JR', 20),
(60839453, 'Charles Harris', 'Architecture', 'SR', 22),
(51135593, 'Maria White', 'English', 'SR', 21);

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

INSERT INTO grades
(`snum`, `cname`, `score`)
VALUES
(574489456, 'Urban Economics', 45),
(567354612, 'Operating System Design', 98),
(567354612, 'Data Structures', 100),
(552455318, 'Operating System Design', 98),
(552455318, 'Communication Networks', 87),
(455798411, 'Operating System Design', 100),
(455798411, 'Optical Electronics', 87),
(348121549, 'Database Systems', 90),
(322654189, 'Database Systems', 97),
(322654189, 'Operating System Design', 56),
(301221823, 'Perception', 87),
(301221823, 'Social Cognition', 87),
(115987938, 'Database Systems', 100),
(115987938, 'Operating System Design', 98),
(112348546, 'Database Systems', 80),
(112348546, 'Operating System Design', 35),
(99354543, '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 |
+------------------------+----------------+----+---------+---+

Answer 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.name, 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, 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.name, 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        |