sql语句，各取前10条记录

[feifan4393]

Id Vl
1 value1
1 value2
...
1 value20
2 value21
2 value22
...
2 value40
3 value41
3 value42
...
3 value60
.
.
.

注：Id从1至5000
每个Id对应Vl值10条以上（20条），共10万条记录
问题：得到Id为1的前10条，为2的前10条，为3的前10条，……，为5000的前10条
请问Sql语句怎么写？

[pt1314917]

--> 测试数据: @a
declare @a table (id int,v1 varchar(1))
insert into @a
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 1,'d' union all
select 2,'a' union all
select 2,'b' union all
select 2,'c' union all
select 2,'d'

select * from @a a where v1 in (select top 2 v1 from @a where id=a.id order by v1)


--结果：
id          v1
----------- ----
1           a
1           b
2           a
2           b

[肥龙上天]

DECLARE @TB TABLE([Id] INT, [Vl] VARCHAR(7))
INSERT @TB 
SELECT 1, 'value1' UNION ALL 
SELECT 1, 'value2' UNION ALL 
SELECT 1, 'value20' UNION ALL 
SELECT 2, 'value21' UNION ALL 
SELECT 2, 'value22' UNION ALL 
SELECT 2, 'value40' UNION ALL 
SELECT 3, 'value41' UNION ALL 
SELECT 3, 'value42' UNION ALL 
SELECT 3, 'value60'



select id,vl from @tb a
where (select count(1) from tb where id = a.id and vl >a.vl) < 10
/*id          vl
----------- -------
1           value1
1           value2
1           value20
2           value21
2           value22
2           value40
3           value41
3           value42
3           value60

(9 row(s) affected)*/

[htl258_Tony]

---------------------------------
--  Author: liangCK 小梁
--  Title : 查每个分组前N条记录
--  Date  : 2008-11-13 17:19:23
---------------------------------

--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (ID VARCHAR(3),GID INT,Author VARCHAR(29),Title VARCHAR(39),Date DATETIME)
INSERT INTO #T
SELECT '001',1,'邹建','深入浅出SQLServer2005开发管理与应用实例','2008-05-10' UNION ALL
SELECT '002',1,'胡百敬','SQLServer2005性能调校','2008-03-22' UNION ALL
SELECT '003',1,'格罗夫Groff.J.R.','SQL完全手册','2009-07-01' UNION ALL
SELECT '004',1,'KalenDelaney','SQLServer2005技术内幕存储引擎','2008-08-01' UNION ALL
SELECT '005',2,'Alex.Kriegel.Boris.M.Trukhnov','SQL宝典','2007-10-05' UNION ALL
SELECT '006',2,'飞思科技产品研发中心','SQLServer2000高级管理与开发','2007-09-10' UNION ALL
SELECT '007',2,'胡百敬','SQLServer2005数据库开发详解','2008-06-15' UNION ALL
SELECT '008',3,'陈浩奎','SQLServer2000存储过程与XML编程','2005-09-01' UNION ALL
SELECT '009',3,'赵松涛','SQLServer2005系统管理实录','2008-10-01' UNION ALL
SELECT '010',3,'黄占涛','SQL技术手册','2006-01-01'

--SQL查询如下:

--按GID分组,查每个分组中Date最新的前2条记录


--1.字段ID唯一时:
SELECT * FROM #T AS T WHERE ID IN(SELECT TOP 2 ID FROM #T WHERE GID=T.GID ORDER BY Date DESC)

--2.如果ID不唯一时:
SELECT * FROM #T AS T WHERE 2>(SELECT COUNT(*) FROM #T WHERE GID=T.GID AND Date>T.Date)

--SQL Server 2005 使用新方法

--3.使用ROW_NUMBER()进行排位分组
SELECT ID,GID,Author,Title,Date
FROM
(
   SELECT rid=ROW_NUMBER() OVER(PARTITION BY GID ORDER BY Date DESC),*
   FROM #T
) AS T
WHERE rid<=2

--4.使用APPLY
SELECT DISTINCT b.*
FROM #T AS a
CROSS APPLY
(
    SELECT TOP(2) * FROM #T WHERE a.GID=GID ORDER BY Date DESC
) AS b


--结果
/*

ID   GID         Author                        Title                                   Date
---- ----------- ----------------------------- --------------------------------------- -----------------------
003  1           格罗夫Groff.J.R.                 SQL完全手册                                 2009-07-01 00:00:00.000
004  1           KalenDelaney                  SQLServer2005技术内幕存储引擎                   2008-08-01 00:00:00.000
005  2           Alex.Kriegel.Boris.M.Trukhnov SQL宝典                                   2007-10-05 00:00:00.000
007  2           胡百敬                           SQLServer2005数据库开发详解                    2008-06-15 00:00:00.000
009  3           赵松涛                           SQLServer2005系统管理实录                     2008-10-01 00:00:00.000
010  3           黄占涛                           SQL技术手册                                 2006-01-01 00:00:00.000

(6 行受影响)
*/

[sdhdy]

--或者

select * from tb a where v1 in (select top 10 v1 from tb where id=a.id order by v1)

[yanleiyigan]

05以上的话用PARTITION BY最简单了

[ai_li7758521]

with temp 
as 
(
	select num=row_number() over (partition by Id order by v1),* from tb
)
select * from temp where num<=10

[xiequan2]

cross apply
select * from (select distinct id from tb11 ) a cross apply (select top 2 * from tb11  where id=a.id order by v1) as ca

[ks_reny]

[Quote=引用 13 楼 sdhdy 的回复:]
--或者

SQL code select * from tb a where v1 in (select top 10 v1 from tb where id=a.id order by v1)
[/Quote]
支持.

[cxudong]

select * from table a where vl>=(select min(vl) from table b where id=a.id and b.vl>=a.vl having count(*)<=10)

[nalnait]

------
DECLARE @TB TABLE(ID INT,V1 VARCHAR(50))
DECLARE @X INT,@Y VARCHAR(50),@Z INT
SET @Z=0

WHILE @Z<30
	BEGIN
		SET @Y='value'
        SET @X=0;

		WHILE @X<20
			BEGIN
				INSERT INTO @TB
					SELECT @Z,@y+cast(@x as varchar)
				SET @X=@X+1
			END
      PRINT CAST(@Z AS VARCHAR)
 	SET @Z=@Z+1
	END

SELECT * FROM (
SELECT ROW_NUMBER()OVER(PARTITION BY ID  ORDER BY ID)XX,* FROM @TB)D 
WHERE XX<11
---------------------------
/*
XX                   ID          V1
-------------------- ----------- --------------------------------------------------
1                    0           value0
2                    0           value1
3                    0           value2
4                    0           value3
5                    0           value4
6                    0           value5
7                    0           value6
8                    0           value7
9                    0           value8
10                   0           value9
1                    1           value0
2                    1           value1
3                    1           value2
4                    1           value3
5                    1           value4
6                    1           value5
7                    1           value6
8                    1           value7
9                    1           value8
10                   1           value9
1                    2           value0
2                    2           value1
*/

[zxkid]

学习了。。。

[Zoezs]

DECLARE @TB TABLE([Id] INT, [Vl] VARCHAR(7))
INSERT @TB 
SELECT 1, 'value1' UNION ALL 
SELECT 1, 'value2' UNION ALL 
SELECT 1, 'value20' UNION ALL 
SELECT 2, 'value21' UNION ALL 
SELECT 2, 'value22' UNION ALL 
SELECT 2, 'value40' UNION ALL 
SELECT 3, 'value41' UNION ALL 
SELECT 3, 'value42' UNION ALL 
SELECT 3, 'value60'

SELECT *,ID2=IDENTITY(int,1,1) INTO # FROM @TB

SELECT Id,[Vl]
FROM (
SELECT *,SEQ=ID2-(SELECT COUNT(*) FROM # WHERE Id<TA.Id)
FROM # AS TA
) TB
WHERE SEQ<=10

DROP TABLE #

[青梦奇缘]

mark!

[sherrysy720]

今天刚刚用了这个内容 row_number() 配合 partition by 05用起来最方便了 大部分都是转载，实际使用以后才能理解

[lsd123]

.

[cuihai198614]

刚学不久，不才懂。

[mengxj85]

学习

[claro]

帮顶

[深夜情感老师]

好多的子查询哦，呵呵……不过看起来没有几个知识点哦都是在复用

[feifan4393]

各位大虾的回复让我受益匪浅，也甚是感激。所有回帖中提到的可行方法我都做了测试，现总结如下：
1楼：很全。不过最后一句（第6点）就可以解决我的问题了。谢谢

2楼、3楼、4楼、5楼:属同一方法，即都使用了row_number() over()，同1楼
简洁明了，效率很高（执行耗时：640毫秒），很实用。So good!

with a as
(select *,row_number() over(PARTITION by ID order by vl) row from @tb)
select id,vl from a where row<=10 order by id 

6楼：介绍了在2000中的解决方法；虽然我用不上（我没有测试），不过还是很感谢；

7楼：同1、2、3、4、5楼。

8楼：是一种新的思路，新方法；可以得到同样的结果，但效率低于row_number() over()，执行耗时：4610毫秒；不过还是要谢谢 pt1314917

select * from @tb a where vl in 
(select top 10 vl from @tb where id=a.id order by vl) order by id

9楼、10楼：同8楼。

11楼：也是一种新方法；可以得到同样的结果，效率介于前两种之间。执行耗时：1843毫秒

select id,vl from @tb a
where (select count(1) from @tb where id = a.id and vl >a.vl) < 10

12楼：很全面，把各种情况进行了分类，并给出解决方法。Perfect! 非常感谢！！强力推荐！！
测试其中的方法：
执行耗时：986毫秒

select * from 
(select distinct id from @tb ) a cross apply (select top 10 * from @tb where id=a.id order by vl) 
as ca order by a.id

13楼：同8楼。
15楼：同1、2、3、4、5楼。

16楼：同12楼。
17楼：同8楼。

18楼：也是一种新方法；可以得到同样的结果，效率还可以。执行耗时：2513毫秒 。感谢！

select * from @tb as a where vl>=(select min(vl) from @tb as b where id=a.id and b.vl>=a.vl having count(*) <=10)
order by a.id

19楼:同1、2、3、4、5楼。

21楼：我没看懂，高手可以指点一下。

注：以上测试记均在同等环境下测试。