Mysql Top N

此MYSQL基于5.7， only_full_group_by模式

Having 和 Where 区别 ： https://blog.csdn.net/jdjh1024/article/details/76647866

准备数据

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

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

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

2.子查询

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

TOP N

1.连接查询-某门课程的TOP-N

select a.name,a.course,a.score
    from test1 a left join test1 b on a.course=b.course and a.score<b.score where a.course = '语文'
    group by a.name,a.course,a.score
    having count(b.id)<2
    order by a.course,a.score desc

1.连接查询-各个课程的TOP-N

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

 

3.子查询-各个课程的第一名

select *
    from test1 a
    where 0 = (select count(*) from test1 where course=a.course and score>a.score)
    order by a.course,a.score desc;

4.子查询-各个课程的第二名
select *
    from test1 a
    where 1 = (select count(*) from test1 where course=a.course and score>a.score)
    order by a.course,a.score desc;

 

5.子查询-各个课程的TOP2
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;