Mysql Top N
原创鱼满仓 最后发布于2019-05-09 10:45:29 阅读数 615 收藏
展开
此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;

 
————————————————
版权声明：本文为CSDN博主「鱼满仓」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/luolaifa000/article/details/90022845

MySQL获取分组后的TOP N记录

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

select advertiser_id,server_update_time from ad_plan a
where 2> (select count(*) from ad_plan where advertiser_id=a.advertiser_id and server_update_time>a.server_update_time)
ORDER by advertiser_id,server_update_time DESC

非分组topN记录或从第几条到第几条

select * from ad_plan limit 1,1 （获取第二条，第一个1为起始offset，第二个1为取1条）
select * from ad_plan limit 0,2 （获取前两条）