如何找出每一组数据的前n条数据？

[ASSASSIN_GEN]

比如找出CSDN用户当天发的前10个帖子？用一句sql怎么实现不用游标循环。

[Limpire]

DECLARE @Test TABLE (C1 int, C2 int)
INSERT @Test SELECT 1, 11 UNION ALL
SELECT 2, 22 UNION ALL
SELECT 2, 21 UNION ALL
SELECT 3, 33 UNION ALL
SELECT 3, 32 UNION ALL
SELECT 3, 31 UNION ALL
SELECT 4, 42 UNION ALL
SELECT 4, 41 UNION ALL
SELECT 5, 51
SELECT * FROM @Test
SELECT * FROM @Test a WHERE C2 IN (SELECT TOP 2 C2 FROM @Test WHERE C1 = a.C1) ORDER BY C1, C2
/*
1．C2为主键，C1有索引的情况下，运行速度最快；
1．C2为主键，C1没有索引的情况下，运行速度超慢；
*/

[Limpire]

--上面有点乱，看下面的，根据C1分组，提取前2条记录：
DECLARE @Test TABLE (C1 int, C2 int)
INSERT @Test
SELECT 1, 11 UNION ALL
SELECT 1, 12 UNION ALL
SELECT 1, 13 UNION ALL
SELECT 2, 21 UNION ALL
SELECT 2, 22 UNION ALL
SELECT 2, 23 UNION ALL
SELECT 3, 31 UNION ALL
SELECT 3, 32 UNION ALL
SELECT 3, 33
SELECT * FROM @Test a WHERE C2 IN (SELECT TOP 2 C2 FROM @Test WHERE C1 = a.C1) ORDER BY C1, C2

[hellowork]

假设每个用户的前3个帖子:
----创建测试数据
declare @t table(id varchar(10), dt datetime)
insert @t
select '1001', '2007-08-21 19:19:02' union all
select '1001', '2007-08-21 19:19:03' union all
select '1001', '2007-08-21 19:19:04' union all
select '1001', '2007-08-21 19:19:05' union all
select '1002', '2007-08-21 19:19:02' union all
select '1002', '2007-08-21 19:19:03' union all
select '1002', '2007-08-21 19:19:04' union all
select '1002', '2007-08-21 19:19:05' union all
select '1002', '2007-08-21 19:19:06'

----方法1:
select * from @t as a where (select count(*) from @t where ID = a.ID and dt > a.dt) < 3
----方法2:
select * from @t as a where not exists(select 1 from @t where ID = a.ID and dt > a.dt group by ID having count(*) > 2)
----方法3:
select * from @t as a where dt in(select top 3 dt from @t where ID = a.ID)

/*结果
id dt
---------- ------------------------
1001 2007-08-21 19:19:02.000
1001 2007-08-21 19:19:03.000
1001 2007-08-21 19:19:04.000
1002 2007-08-21 19:19:02.000
1002 2007-08-21 19:19:03.000
1002 2007-08-21 19:19:04.000
*/

[hellowork]

抱歉,更正一下:
----创建测试数据
declare @t table(id varchar(10), dt datetime)
insert @t
select '1001', '2007-08-21 19:19:02' union all
select '1001', '2007-08-21 19:19:03' union all
select '1001', '2007-08-21 19:19:04' union all
select '1001', '2007-08-21 19:19:05' union all
select '1002', '2007-08-21 19:19:02' union all
select '1002', '2007-08-21 19:19:03' union all
select '1002', '2007-08-21 19:19:04' union all
select '1002', '2007-08-21 19:19:05' union all
select '1002', '2007-08-21 19:19:06'

----方法1:
select * from @t as a where (select count(*) from @t where ID = a.ID and dt > a.dt) < 3
----方法2:
select * from @t as a where not exists(select 1 from @t where ID = a.ID and dt > a.dt group by ID having count(*) > 2)
----方法3:
select * from @t as a where dt in(select top 3 dt from @t where ID = a.ID order by dt DESC)

/*结果
id dt
---------- ------------------------
1001 2007-08-21 19:19:03.000
1001 2007-08-21 19:19:04.000
1001 2007-08-21 19:19:05.000
1002 2007-08-21 19:19:04.000
1002 2007-08-21 19:19:05.000
1002 2007-08-21 19:19:06.000
*/