分组 GROUP BY 与 HAVING

← 返回 Python3 MySQL 数据库

57.17 分组 GROUP BY 与 HAVING

GROUP BY 列 按列分组，常配合 COUNT/AVG/SUM 做分组统计。

HAVING 过滤分组结果（类似 WHERE，但用于聚合之后）。

SELECT 中非聚合列一般应出现在 GROUP BY 中。

每班人数与平均分

import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', password='root', database='py_demo', charset='utf8mb4')
try:
    with conn.cursor() as cur:
        cur.execute('''
            SELECT class_name,
                   COUNT(*) AS num,
                   AVG(score) AS avg_score,
                   MAX(score) AS top_score
            FROM students
            GROUP BY class_name
            HAVING AVG(score) >= 70
            ORDER BY avg_score DESC
        ''')
        for row in cur.fetchall():
            print(row)
finally:
    conn.close()