mysql实现topN top1

最新推荐文章于 2024-01-04 10:02:14 发布

dengdun2029

最新推荐文章于 2024-01-04 10:02:14 发布

阅读量2.2k

点赞数 1

CC 4.0 BY-SA版权

文章标签：数据库大数据

原文链接：http://www.cnblogs.com/codeOfLife/p/7410620.html

本文介绍在MySQL中如何实现查询分组后的最大值、最小值所在整行记录及分组后的TopN行记录。通过自连接、子查询、union all、自身左连接和用户变量等方法，详细解析了查询每门课程最高分学生及其成绩，以及每门课程前两名学生的成绩实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

　　有时会碰到一些需求，查询分组后的最大值，最小值所在的整行记录或者分组后的top n行的记录，像在hive中是有窗口函数的，可以通过它们来实现，但是MySQL没有这些函数，可通过下面的方法来实现

1、准备

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


insert into test1(name,course,score)
values 
('张三','语文',80),
('李四','语文',90),
('王五','语文',93),
('张三','数学',77),
('李四','数学',68),
('王五','数学',99),
('张三','英语',90),
('李四','英语',50),
('王五','英语',89);

2、TOP 1

需求：查询每门课程分数最高的学生以及成绩

实现方法：可以通过自连接、子查询来实现，如下

a、自连接实现

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;

　执行效果如下

b、子查询实现

select name,course,score 
from test1 a  
where score=(select max(score) from test1 where a.course=test1.course);

执行效果如下

也可以用下面这个子查询实现

select name,course,score 
from test1 a 
where not exists(select 1 from test1 where a.course=test1.course and a.score < test1.score);

执行效果如下

3、TOP N

需求：查询每门课程前两名的学生以及成绩

实现方式：使用union all、自身左连接、子查询、用户变量等方式实现

a、使用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)
union all
(select name,course,score from test1 where course='英语' order by score desc limit 2);

执行效果如下

b、使用自身左连接

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;

执行效果如下

c、使用子查询

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;

执行效果如下

d、使用用户变量

set @num := 0, @course := '';

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;