SQL分组查询最值

有时候我们会遇到这样一种情况，比如说需要你查询每个班级中成绩最好的学生信息，这个时候我们就要用到分组查询。

首先创建一个表t_class_user_score：

CREATE TABLE t_class_user_score (
	class_id int(4) NULL,      -- 班级ID
	user_id int(4) NULL,       -- 学号
	name varchar(100) NULL,    -- 学生姓名
	score int(4) NULL          -- 成绩 
);

这个表用来存放学生的信息以及成绩。

然后我们为这个表初始化一些数据：

INSERT INTO t_class_user_score(class_id, user_id, name, score) VALUES(1, 1001, '张三', 77);
INSERT INTO t_class_user_score(class_id, user_id, name, score) VALUES(1, 1002, '李四', 86);
INSERT INTO t_class_user_score(class_id, user_id, name, score) VALUES(1, 1003, '王五', 48);
INSERT INTO t_class_user_score(class_id, user_id, name, score) VALUES(2, 1004, '赵六', 92);
INSERT INTO t_class_user_score(class_id, user_id, name, score) VALUES(2, 1005, '陈元', 84);
INSERT INTO t_class_user_score(class_id, user_id, name, score) VALUES(2, 1006, '刘其', 96);
INSERT INTO t_class_user_score(class_id, user_id, name, score) VALUES(2, 1006, '刘其', 88);
INSERT INTO t_class_user_score(class_id, user_id, name, score) VALUES(3, 1007, '胡晨', 88);
INSERT INTO t_class_user_score(class_id, user_id, name, score) VALUES(3, 1008, '愣头青', 58);
INSERT INTO t_class_user_score(class_id, user_id, name, score) VALUES(3, 1008, '愣头青', 76);

 

现在我们要查找每个班成绩最好(或最差)的同学，可以这样写：

-- 查询每个班成绩最好的同学
SELECT class_id,user_id,name,score FROM t_class_user_score AS t1 WHERE NOT EXISTS (
	SELECT 1 FROM t_class_user_score AS t2 WHERE t1.class_id = t2.class_id AND t1.score < t2.score 
) 
ORDER BY class_id 

-- 查询每个班成绩最差的同学
SELECT class_id,user_id,name,score FROM t_class_user_score AS t1 WHERE NOT EXISTS (
	SELECT 1 FROM t_class_user_score AS t2 WHERE t1.class_id = t2.class_id AND t1.score > t2.score 
) 
ORDER BY class_id 

-- 查询每个班成绩最好和最差的同学
SELECT class_id,user_id,name,score FROM (
	SELECT class_id,user_id,name,score FROM t_class_user_score AS t1 WHERE NOT EXISTS (
		SELECT 1 FROM t_class_user_score AS t2 WHERE t1.class_id = t2.class_id AND t1.score < t2.score 
	) 
	UNION ALL 
	SELECT class_id,user_id,name,score FROM t_class_user_score AS t1 WHERE NOT EXISTS (
		SELECT 1 FROM t_class_user_score AS t2 WHERE t1.class_id = t2.class_id AND t1.score > t2.score 
	) 
) AS c
ORDER BY c.class_id,c.score 

这个SQL其实就是类似一个子查询，查询当前组内(这里的当前组就是条件WHERE t1.class_id = t2.class_id)是否存在比当前数据大或小的数据，如果没有就命中，这个过程也有点类似一个双重循环。

 

查询结果分别如下：

查询每个班成绩最好的同学

查询每个班成绩最差的同学

查询每个班成绩最好和最差的同学