分组筛选的异同
2019-03-26 本文已影响0人
咖A喱
语法
SELECT 分组函数，查询字段，
FROM 表
WHERE 筛选条件
GROUP BY 分组的字段
HAVING 分组函数的筛选条件
ORDER BY 排序的字段
特点：
- GROUP BY后跟分组函数查询的字段
- 分组可以按单个字段也可以多个字段
| 使用的表 | 位置 | 关键字 | |
|---|---|---|---|
| 分组前筛选 | 原始表 | group by 之前 | where |
| 分组后筛选 | group by后的结果集 | group by 之后 | having |
案例
CREATE DATABASE IF NOT EXISTS students DEFAULT CHARSET UTF8 COLLATE UTF8_GENERAL_CI ;
USE students;
DROP TABLE IF EXISTS student_info;
CREATE TABLE student_info(
`name` VARCHAR(10) NOT NULL,
id INT NOT NULL,
chinese_score FLOAT NOT NULL,
math_score FLOAT NOT NULL,
english_score FLOAT NOT NULL);
INSERT INTO student_info VALUES ('张三',1,1,98),('李四',2,1,89),('王五',3,1,56),('代',4,1,78),
('开发',5,2,96),('大彻',6,2,76),('到底',7,2,46),
('隔热片',8,3,78),('功夫',9,3,85),('粉丝',10,3,91),
('丰富',11,4,82),('废惹',12,4,77),('大大',13,4,76),
('光荣',14,5,95),('分隔',15,5,72),('苹果',16,5,86);
SELECT * FROM student_info;
name id class_id chinese_score
--------- ------ -------- ---------------
张三 1 1 98
李四 2 1 89
王五 3 1 56
代 4 1 78
开发 5 2 96
大彻 6 2 76
到底 7 2 46
隔热片 8 3 78
功夫 9 3 85
粉丝 10 3 91
丰富 11 4 82
废惹 12 4 77
大大 13 4 76
光荣 14 5 95
分隔 15 5 72
苹果 16 5 86
分组前查询
- 查询班级id为1和2的学生的总个数
SELECT COUNT(*) AS 总数,class_id AS 班级
FROM student_info
WHERE class_id IN(1,2)
GROUP BY 班级;
总数 班级
------ --------
4 1
3 2
- 查询每个班级语文成绩及格（分数>60）学生成绩的平均数
SELECT class_id AS 班级,ROUND(AVG(chinese_score),2) AS 语文成绩平均数
FROM student_info
WHERE chinese_score > 60
GROUP BY 班级
SELECT class_id AS 班级,ROUND(AVG(chinese_score),2) AS 语文成绩平均数
FROM student_info
WHERE chinese_score > 60
GROUP BY 班级
班级 语文成绩平均数
------ -----------------------
1 88.33
2 86.00
3 84.67
4 78.33
5 84.33
班级 原始语文成绩平均数
------ -----------------------------
1 80.25
2 72.67
3 84.67
4 78.33
5 84.33
分组后查询
- 查询语文成绩平均分大于75的班级
SELECT class_id AS 班级,ROUND(AVG(chinese_score),2) AS 语文成绩平均分
FROM student_info
GROUP BY 班级
HAVING 语文成绩平均分 > 75;
班级 语文成绩平均分
------ -----------------------
1 80.25
3 84.67
4 78.33
5 84.33
- 查询每个班级的语文成绩最高分，并按照降序排序
SELECT class_id AS 班级,MAX(chinese_score) AS 最高分
FROM student_info
GROUP BY 班级
HAVING 最高分 > 90
ORDER BY 最高分 DESC;
班级 最高分
------ -----------
1 98
2 96
5 95
3 91
