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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了在具有多个条件的MySQL中使用group by子句的正确方法是什么？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4672字，纯文字阅读大概需要7分钟。

内容图文

我正在编写一个查询,如果每个专业的平均成绩均超过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 |
+------------------------+----------------+----+---------+---+

解决方法:

这是一种适用于您的用例的方法.逻辑是将聚合和窗口函数结合在一起.

首先,您可以使用简单的汇总查询来计算每个学生的平均分数：

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        |

内容总结

以上是互联网集市为您收集整理的在具有多个条件的MySQL中使用group by子句的正确方法是什么？全部内容，希望文章能够帮你解决在具有多个条件的MySQL中使用group by子句的正确方法是什么？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端