如何理解这段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

详细见  http://blog.sina.com.cn/s/blog_412897e10100r2rq.html

如何理解这个 
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 这条语句？
希望大家都来讨论下

6 个解决方案

#1

使用ROW_NUMBER() over()简单多了

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

/*
id	title	typeid	datetime	rn
3	1.3	1	3	1
2	1.2	1	2	2
6	2.3	2	6	1
5	2.2	2	5	2
9	3.3	3	9	1
8	3.2	3	8	2
*/

#2

如果是sql server 2005及以后的版本，建议用row_number函数，非常简单，

而且易于理解：

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条数据

/*
id	title	typeid	datetime	rownum
3	1.3	1	3	1
2	1.2	1	2	2
6	2.3	2	6	1
5	2.2	2	5	2
9	3.3	3	9	1
8	3.2	3	8	2
*/

#3

关于语句的理解，就是对于外层的a表，在内层的b表中，查找typeid相同，比b中的datetime小的记录条数，

如果是小于等于1，那么就返回这条记录，实际上对于datetime最大的那条记录，count(*) 会返回0，因为这条记录不比任何其他记录的datetime小，

而datetime第二大的记录，count(*)会返回1，因为只有datetime最大的那条记录，是datetime大于它的，所以返回1.

所以最后对于每组typeid，都会返回datetime最大的，和第二大的，记录

#4

引用 楼主 mayee 的回复:

如何理解这个 
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 这条语句？
希望大家都来讨论下

--> test2中,每笔记录都计算一次同typeid下大于本datetime的记录数,取记录数<=1的,
    即0和1,0表示datetime最大的,1表示datetime第2的,也就是是最大的前2条.

#5

哪需要介么麻烦，这样就OK了，语句也易懂
SELECT [id], [title], [typeid], [datetime] FROM [xahh].[dbo].[test2] T 
FROM (
  SELECT typeid,MAX(datetime) datetime FROM [xahh].[dbo].[test2] 
  GROUP BY typeid
) S ON S.typeid=T.typeid AND S.datetime=T.datetime

#6

十分感谢诸位热心的回答，很受用。结贴