创建了一个名为Classes的表，包含ClassID、Dept、Level和TotalStudents字段。接着插入了一些数据，展示不同部门和级别的班级最大学生数。使用Max()函数结合GROUP BY和ROLLUP子句，按部门和级别分组计算每个组的最大学生数，最后展示了查询结果并删除了Classes表。
mysql> CREATE TABLE Classes
-> (
-> ClassID SMALLINT NOT NULL PRIMARY KEY,
-> Dept CHAR(4) NOT NULL,
-> Level ENUM('Upper', 'Lower') NOT NULL,
-> TotalStudents TINYINT UNSIGNED NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO Classes VALUES
-> (1001, 'ANTH', 'Upper', 25),
-> (1002, 'ANTH', 'Upper', 25),
-> (1003, 'MATH', 'Upper', 18),
-> (1004, 'ANTH', 'Lower', 19),
-> (1005, 'ENGL', 'Upper', 28),
-> (1006, 'MATH', 'Lower', 23),
-> (1007, 'ENGL', 'Upper', 25),
-> (1008, 'MATH', 'Lower', 29),
-> (1009, 'ANTH', 'Upper', 25),
-> (1010, 'ANTH', 'Lower', 30),
-> (1011, 'ENGL', 'Lower', 26),
-> (1012, 'MATH', 'Lower', 22),
-> (1013, 'ANTH', 'Upper', 27),
-> (1014, 'ANTH', 'Upper', 21),
-> (1015, 'ENGL', 'Lower', 25),
-> (1016, 'ENGL', 'Upper', 32);
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> SELECT Dept, Level, MAX(TotalStudents) AS Maximum
-> FROM Classes
-> GROUP BY Dept, Level WITH ROLLUP; --//Max()函数求最大值
+------+-------+---------+
| Dept | Level | Maximum |
+------+-------+---------+
| ANTH | Upper | 27 |
| ANTH | Lower | 30 |
| ANTH | NULL | 30 |
| ENGL | Upper | 32 |
| ENGL | Lower | 26 |
| ENGL | NULL | 32 |
| MATH | Upper | 18 |
| MATH | Lower | 29 |
| MATH | NULL | 29 |
| NULL | NULL | 32 |
+------+-------+---------+
10 rows in set (0.00 sec)
mysql> drop table Classes;
Query OK, 0 rows affected (0.00 sec)
扫一扫
238
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
