--参考
取n到m行1.
select top m * from tablename where id not in (select top n id from tablename order by id asc/*|desc*/) 2.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入到临时表
set rowcount n --只取n条结果
select * from 表变量 order by columnname desc 3.
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc
4.如果tablename里没有其他identity列,那么:
先生成一个序列,存储在一临时表中.
select identity(int) id0,* into #temp from tablename 取n到m条的语句为:
select * from #temp where id0 > =n and id0 <= m 如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行:
exec sp_dboption 你的DB名字,'select into/bulkcopy',true
5.如果表里有identity属性,那么简单:
select * from tablename where identity_col between n and m 6.SQL2005开始.可以使用row_number() over()生成行号
;with cte as
(
select id0=row_number() over(order by id),* from tablename
)
select * from cte where id0 between n to m
取n到m行1.
select top m * from tablename where id not in (select top n id from tablename order by id asc/*|desc*/) 2.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入到临时表
set rowcount n --只取n条结果
select * from 表变量 order by columnname desc 3.
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc
4.如果tablename里没有其他identity列,那么:
先生成一个序列,存储在一临时表中.
select identity(int) id0,* into #temp from tablename 取n到m条的语句为:
select * from #temp where id0 > =n and id0 <= m 如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行:
exec sp_dboption 你的DB名字,'select into/bulkcopy',true
5.如果表里有identity属性,那么简单:
select * from tablename where identity_col between n and m 6.SQL2005开始.可以使用row_number() over()生成行号
;with cte as
(
select id0=row_number() over(order by id),* from tablename
)
select * from cte where id0 between n to m
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 (
SELECT *,SEQ=ROW_NUMBER() OVER (PARTITION BY Id ORDER BY GETDATE())
FROM @TB
) T
WHERE SEQ<=10
(select *,row_number() over(PARTITION by ID order by vl) row from tb)
select id,vl from a where row<=10
wang as (select id=row_number() over (partition by Id order by getdate()),* from tb)
select * from wang where row<=10
select ID,V1 from
(select *,px = row_number() over(partition by id order by ID) from Table)T
where px between 1 and 10
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 @TBSELECT Id,[Vl]
FROM (
SELECT *,SEQ=ID2-(SELECT COUNT(*) FROM # WHERE Id<TA.Id)
FROM # AS TA
) TB
WHERE SEQ<=10DROP TABLE #
select * from 表名 a where v1 in (select top 10 v1 from 表名 where id=a.id order by v1)
--> 测试数据: @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
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)*/
-- 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 行受影响)
*/
select * from tb a where v1 in (select top 10 v1 from tb where id=a.id order by v1)
as
(
select num=row_number() over (partition by Id order by v1),* from tb
)
select * from temp where num<=10
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
------
DECLARE @TB TABLE(ID INT,V1 VARCHAR(50))
DECLARE @X INT,@Y VARCHAR(50),@Z INT
SET @Z=0WHILE @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
ENDSELECT * 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
*/
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 @TBSELECT Id,[Vl]
FROM (
SELECT *,SEQ=ID2-(SELECT COUNT(*) FROM # WHERE Id<TA.Id)
FROM # AS TA
) TB
WHERE SEQ<=10DROP TABLE #
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 id9楼、10楼:同8楼。11楼:也是一种新方法;可以得到同样的结果,效率介于前两种之间。执行耗时:1843毫秒
select id,vl from @tb a
where (select count(1) from @tb where id = a.id and vl >a.vl) < 1012楼:很全面,把各种情况进行了分类,并给出解决方法。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.id13楼:同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.id19楼:同1、2、3、4、5楼。21楼:我没看懂,高手可以指点一下。注:以上测试记均在同等环境下测试。