mysql分组求topN详解

数据准备

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);

TOP 1

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

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

• 自连接实现

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;

• 子查询实现

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);

或者

select name,course,score from test1 a
where 1 > (select count(*) from test1 where a.course=test1.course and test1.score > a.score);

TOP N

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

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

• 使用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);

• 使用自身左连接

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;

• 使用子查询

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;

• 使用用户变量

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;

分析top n子查询

select * from test1 a 
where 2 > (select count(*) from test1 where course=a.course and score>a.score)

分析下这个sql：

相关子查询的特点就是子查询依赖与外部查询，在这里面其实是 select * from test 已经先执行了一遍了，查出了所有的数据

然后相关子查询针对每一行数据进行select count(*) from test1 where course=a.course and score>a.score

例如：

　　第一行是张三，数学77，那么相关子查询做的工作就是找出test表所有课程是数学的行，查询 张三，77|李四，68|王五，99

　　然后where条件score>77，查询出王五，99，count=1，这时候外部条件2>1,符合。

　　第二行是李四，数学68，那么相关子查询做的工作就是找出test表所有课程是数学的行，查询 张三，77|李四，68|王五，99

　　然后where条件score>68，查询出张三，77,王五，99，count=2，这时候外部条件2>2,不符合。

　　第三行是王五，数学99，那么相关子查询做的工作就是找出test表所有课程是数学的行，查询 张三，77|李四，68|王五，99

　　然后where条件score>99，没有数据，这时候外部条件2>0,符合。

　　那么就筛选出了数学最大的2个人，张三和王五。

　　

其实这里的子查询就是找出和当前行类型能匹配上的比他大的有多少，没有比他大的他就是最大

那么找top1就是 1>(xxx)，topN就是N>(xxxxx)