有时会碰到一些需求, 查询分组后 的最大值,最小值所在的整行记录或者分组后的top n行的记录,在一些别的数据库可能有窗口函数可以方面的查出来,但是MySQL没有这些函数,没有直接的方法可以查出来,可通过以下的方法来查询。 准备工作测试表结构如下: root:test> show create table test1\G *************************** 1. row *************************** Table: test1 Create Table: CREATE TABLE `test1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `course` varchar(20) DEFAULT NULL, `score` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 插入数据: insert into test1(name,course,score) values ('张三','语文',80), ('李四','语文',90), ('王五','语文',93), ('张三','数学',77), ('李四','数学',68), ('王五','数学',99), ('张三','英语',90), ('李四','英语',50), ('王五','英语',89); 查看结果: root:test> select * from test1; +----+--------+--------+-------+ | id | name | course | score | +----+--------+--------+-------+ | 1 | 张三 | 语文 | 80 | | 2 | 李四 | 语文 | 90 | | 3 | 王五 | 语文 | 93 | | 4 | 张三 | 数学 | 77 | | 5 | 李四 | 数学 | 68 | | 6 | 王五 | 数学 | 99 | | 7 | 张三 | 英语 | 90 | | 8 | 李四 | 英语 | 50 | | 9 | 王五 | 英语 | 89 | +----+--------+--------+-------+ TOP 1查询每门课程 分数最高 的学生以及成绩 1、使用自连接【推荐】 root:test> select a.name,a.course,a.score from -> test1 a -> join (select course,max(score) score from test1 group by course) b -> on a.course=b.course and a.score=b.score; +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 王五 | 语文 | 93 | | 王五 | 数学 | 99 | | 张三 | 英语 | 90 | +--------+--------+-------+ 3 rows in set (0.00 sec) 2、使用相关子查询 root:test> select name,course,score from test1 a -> where score=(select max(score) from test1 where a.course=test1.course); +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 王五 | 语文 | 93 | | 王五 | 数学 | 99 | | 张三 | 英语 | 90 | +--------+--------+-------+ 3 rows in set (0.00 sec) 或者 root:test> select name,course,score from test1 a -> where not exists(select 1 from test1 where a.course=test1.course and a.score < test1.score); +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 王五 | 语文 | 93 | | 王五 | 数学 | 99 | | 张三 | 英语 | 90 | +--------+--------+-------+ 3 rows in set (0.00 sec) TOP NN>=1 查询每门课程前两名的学生以及成绩 1、使用union all 如果结果集比较小,可以用程序查询单个分组结果后拼凑,也可以使用union all root:test> (select name,course,score from test1 where course='语文' order by score desc limit 2) -> union all -> (select name,course,score from test1 where course='数学' order by score desc limit 2) -> union all -> (select name,course,score from test1 where course='英语' order by score desc limit 2); +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 王五 | 语文 | 93 | | 李四 | 语文 | 90 | | 王五 | 数学 | 99 | | 张三 | 数学 | 77 | | 张三 | 英语 | 90 | | 王五 | 英语 | 89 | +--------+--------+-------+ 6 rows in set (0.01 sec) 2、自身左连接 root:test> select a.name,a.course,a.score -> from test1 a left join test1 b on a.course=b.course and a.score<b.score -> group by a.name,a.course,a.score -> having count(b.id)<2 -> order by a.course,a.score desc; +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 王五 | 数学 | 99 | | 张三 | 数学 | 77 | | 张三 | 英语 | 90 | | 王五 | 英语 | 89 | | 王五 | 语文 | 93 | | 李四 | 语文 | 90 | +--------+--------+-------+ 6 rows in set (0.00 sec) 3、相关子查询 root:test> select * -> from test1 a -> where 2>(select count(*) from test1 where course=a.course and score>a.score) -> order by a.course,a.score desc; +----+--------+--------+-------+ | id | name | course | score | +----+--------+--------+-------+ | 6 | 王五 | 数学 | 99 | | 4 | 张三 | 数学 | 77 | | 7 | 张三 | 英语 | 90 | | 9 | 王五 | 英语 | 89 | | 3 | 王五 | 语文 | 93 | | 2 | 李四 | 语文 | 90 | +----+--------+--------+-------+ 6 rows in set (0.01 sec) 4、使用用户变量 root:test> set @num := 0, @course := ''; Query OK, 0 rows affected (0.00 sec) root:test> root:test> select name, course, score -> from ( -> select name, course, score, -> @num := if(@course = course, @num + 1, 1) as row_number, -> @course := course as dummy -> from test1 -> order by course, score desc -> ) as x where x.row_number <= 2; +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 王五 | 数学 | 99 | | 张三 | 数学 | 77 | | 张三 | 英语 | 90 | | 王五 | 英语 | 89 | | 王五 | 语文 | 93 | | 李四 | 语文 | 90 | +--------+--------+-------+ 6 rows in set (0.00 sec)(责任编辑:最模板) |