mysql分组取TOP N个的问题

今天刷题遇到这样的问题:查询各科成绩前两名的记录

思路:

复用表格,获得表a第一条记录的分数,放入表b中做对比,计算表b中分数大于a分数的个数,如表b中大于a分数共有2条记录,说明a排名第三。

现在要分组(分科目cid)查前两名,有两个限制条件:(1)cid要相同来进行比较;(2)表b中大于a分数的只能有0或1条记录。

要注意的是分数相同是否算相同排名,如果出现第1、2分数相同,第3、4个分数相同的情况,而我们想要取第1234的记录作为前两名(也就是,分数相同算相同排名),就要在count处加上distinct,否则只会输出第1、2个记录。

select a.*
from score a
where (select count(*) from score  where score>a.score and a.cid=cid)<2
order by cid

而且这种方法效率很低,数据量一大很不实用,下面牛客网的题目进行了验证,面对这种题目最好还是用用户变量的方法,就是给表a和b都加上一列rownum。当然最简洁的就是用窗口函数直接分组,组内排序,再过滤排名,但有一些库不支持窗口函数,就使用用户变量法,本质都是添加一个排序列。

这篇文章说得比较好理解,参考参考 mysql 分组获取前三条记录

--分数相同,排名相同的写法
select a.sid,a.cid,a.score
from (
	select score.*,
	(case
		when @pres=score then @currank
		when @pres:=score then @currank:=@currank+1
	end) rownum
	from score,(select @pres:=null,@currank:=0) init
	order by cid,score desc) a
where (select count(distinct b.rownum) from( 
								select score.*,
								(case
									when @pres2=score then @currank2
									when @pres2:=score then @currank2:=@currank2+1
								end) rownum
								from score,(select @pres2:=null,@currank2:=0) init
								order by cid,score desc) b
								where b.rownum<a.rownum and a.cid=b.cid)<2
order by a.cid

另外,这个方法也可以用来得出每条记录的行数,牛客网的 一道 选取奇数或偶数行的题目 用的也是同样方法

21. 选取表的奇数行、偶数行数据

对于employees表中,给出奇数行的first_name
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
输出格式:

first_name
Georgi
Chirstian
Anneke
Tzvetan
Saniya
Mary

两种方法:普通法和临时变量法

(1)普通法(网友答案):(线下用mysql测试,近50w条数据,查询速度非常慢)

select c.first_name
from
(select a.first_name,
    (select count(*)
    from employees b
    where b.first_name<=a.first_name) rownum
from employees a) c
where c.rownum%2=1


--中间不建临时表的写法
select first_name
from employees e1
where (select count(*) from employees e2 where e2.first_name<=e1.first_name)%2=1

思路:首先复用表,先建立新的一列,这一列包含每一行的行数,计算行号的方法 : 有多少个小于等于a.first_name的记录的个数就是a.first_name的行号。例如输入a表第1行进去,此时小于a.first_name的记录数为1;输入a表第二行进去,此时小于a.first_name的记录数共有2个,表示为第2行...下一步是挑选出rownum为奇数的行,即可。

select a.first_name,
    (select count(*)
    from employees b
    where b.first_name<=a.first_name) rownum
from employees a

(2)临时变量法(SQLite无法使用,线下测试通过,查询速度非常快)

select a.first_name
from
(select first_name, (@row:=@row+1) rownum
from employees,(select @row:=0) init) a
where a.rownum%2=1
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页