如何理解这段SQL:取每组前几条记录的SQL写法
有个项目需求,需要将一些重复数据分组,然后取每组前1条。在网上搜到这段代码:
--对于表test2(id是主键),有:
SELECT [id], [title], [typeid], [datetime] FROM [xahh].[dbo].[test2]
id title typeid datetime
1 1.1 1 1
2 1.2 1 2
3 1.3 1 3
4 2.1 2 4
5 2.2 2 5
6 2.3 2 6
7 3.1 3 7
8 3.2 3 8
9 3.3 3 9
--取每个typeid的最大datetime的2条:
--第一种取法:
select * from test2 a where
(select count(*) from test2 b where b.typeid = a.typeid and a.datetime< b.datetime) <=1
order by typeid, datetime desc
--[解释:相同typeid的记录中比该记录datetime小的记录数不能大于1,可以保证该记录在前2条]
--结果
id title typeid datetime
3 1.3 1 3
2 1.2 1 2
6 2.3 2 6
5 2.2 2 5
9 3.3 3 9
8 3.2 3 8
create table #tb([id] int, [title] numeric(12,1), [typeid] int, [datetime] int)
insert into #tb
select 1,1.1,1,1
union all select 2,1.2,1,2
union all select 3,1.3,1,3
union all select 4,2.1,2,4
union all select 5,2.2,2,5
union all select 6,2.3,2,6
union all select 7,3.1,3,7
union all select 8,3.2,3,8
union all select 9,3.3,3,9
select *
from(select *,rn=ROW_NUMBER() over(partition by [typeid] order by [datetime] desc) from #tb)t
where rn<=2
drop table #tb
/*
idtitletypeiddatetimern
31.3131
21.2122
62.3261
52.2252
93.3391
83.2382
*/
if object_id('tb') is not null
drop table tb
go
create table tb
([id] int, [title] numeric(12,1), [typeid] int, [datetime] int)
insert into tb
select 1,1.1,1,1
union all select 2,1.2,1,2
union all select 3,1.3,1,3
union all select 4,2.1,2,4
union all select 5,2.2,2,5
union all select 6,2.3,2,6
union all select 7,3.1,3,7
union all select 8,3.2,3,8
union all select 9,3.3,3,9
select *
from
(
select *,
--先按typeid分组,在一组中按照datetime降序排列,来编号
ROW_NUMBER() over(partition by typeid
order by datetime desc) as rownum
from tb
)t
where rownum<=2 --取行号为1和2的,也就是时间最大的2条数据
/*
idtitletypeiddatetimerownum
31.3131
21.2122
62.3261
52.2252
93.3391
83.2382
*/