如何选取表中符合某个条件的第n条到第n+m条记录？

[TonyTonyQ]

如题

[pengdali]

select IDENTITY(int, 1,1) AS ID_Num,* into #temp from 表 where 某个条件
select * from #temp where ID_Num between 10 and 20

[pengdali]

推荐：

CREATE PROCEDURE GetProductsPaged
@lastProductID int,
@pageSize int
AS
SET ROWCOUNT @pageSize
SELECT *
FROM Products
WHERE [standard search criteria]
AND ProductID > @lastProductID
ORDER BY [Criteria that leaves ProductID monotonically increasing]
GO

[gmlxf]

select * from yourtable
where id
between
(select max(id) from (select top n id from yourtable order by id) a)
and
(select max(id) from (select top n+m id from yourtable order by id) b)

[dafei0320]

同意楼上！~！
select * from yourtable
where id
between
(select max(id) from (select top n id from yourtable order by id) a)
and
(select max(id) from (select top n+m id from yourtable order by id) b)

[wzh1215]

select top m * from yourtable where 条件 and id not in(select top n * from yourtable order by id) order by id

[txlicenhe]

http://expert.csdn.net/Expert/topic/2365/2365596.xml?temp=.5068781
交流--查询第X页,每页Y条记录
邹建

[zjcxc]

/*--用存储过程实现的分页程序

显示指定表、视图、查询结果的第X页
对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法
如果视图或查询结果中有主键,不推荐此方法
如果使用查询语句,而且查询语句使用了order by,则查询语句必须包含top 语句

--邹建 2003.09--*/

/*--调用示例
exec p_show '地区资料'

exec p_show 'select top 100 percent * from 地区资料 order by 地区名称',5,3,'地区编号,地区名称,助记码'
--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_show]
GO

CREATE Proc p_show
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
,@Obj_ID int --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
,@strjoin nvarchar(4000) --连接字段
,@strwhere nvarchar(2000) --查询条件


select @Obj_ID=object_id(@QueryStr)
,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end

--如果显示第一页，可以直接用top来完成
if @PageCurrent=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
return
end

--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))

select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp --如果表中无主键,则用临时表处理

select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
if @@rowcount>1 --检查表中的主键是否为复合主键
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp

/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
exec('select top '+@Id1+@FdShow+' from '+@QueryStr
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
+')'+@FdOrder
)
return

/*--表中有复合主键的处理方法--*/
lbusepk:
exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
left join (select top '+@Id2+' '+@strfd+'
from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return

/*--用临时表处理的方法--*/
lbusetemp:
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))

exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from'+@QueryStr+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)

GO

[zjcxc]

--数据测试环境

--检查对象是否存在,如果存在,删除
if exists (select * from dbo.sysobjects where id = object_id(N'[主表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [主表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[从表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [从表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_qry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_qry]
GO

--创建表环境
create table 主表(id varchar(40) not null constraint PK_主表 primary key
,createdate datetime
)

create table 从表(id varchar(40) not null
,indexid varchar(40) not null
,value varchar(500)
)
alter table 从表 add constraint PK_从表 PRIMARY KEY CLUSTERED (id,indexid)

--插入测试数据
insert into 主表
select '01','2003-10-21'
union all select '02','2003-10-21'

insert into 从表
select '01','1001','asdf'
union all select '01','aby6','abcd'
union all select '01','1003','jsfk'
union all select '01','1t0b','yuet'
union all select '02','1001','jdjd'
union all select '02','1002','ksks'
union all select '02','aby6','hyei'

go
--创建分页查询的存储过程
create proc p_qry
@where varchar(8000)='', --查询的条件,属于主表的字段用a.字段,属于从表的字段用b.字段
@pagesize int=20, --每页的大小
@page int=1, --要查询第几页
@createview bit=1 --是否重建视图,第一次调用时或查询条件变化时指定为1,其他情况指定为0
as
declare @sql varchar(8000)
declare @viewname sysname
set @viewname='tmp_qry_'+host_name()+'_'+user_name() --以用户电脑名+登陆的用户名做视图名
if object_id(@viewname) is null
goto lb_createview
else if @createview=1
begin
set @sql='drop view ['+@viewname+']'
exec(@sql)
goto lb_createview
end
goto lb_qry

lb_createview:
if @where<>'' set @where='where ('+@where+')'
exec('create view ['+@viewname+']
as
select a.*,b.indexid,b.value from 主表 a inner join 从表 b on a.id=b.id
'+@where)

lb_qry:
declare @p1 varchar(20),@p2 varchar(20)
if @page=1
begin
set @p1=cast(@pagesize as varchar)
exec('select top '+@p1+' * from ['+@viewname+']')
end
else
begin
select @p1=cast(@pagesize as varchar)
,@p2=cast((@page-1)*@pagesize as varchar)
exec('select top '+@p1+' * from ['+@viewname+'] a left join
(select top '+@p2+' id,indexid from ['+@viewname+']) b
on a.id=b.id and a.indexid=b.indexid
where b.id is null
')
end
go

--调用测试
exec p_qry @where='',@pagesize=5,@page=2
exec p_qry @where='',@pagesize=5,@page=2
exec p_qry @where='b.indexid=''1002'' and b.value like ''%abcd%'' and a.createdate between ''2003-10-21'' and ''2003-11-21'''
,@pagesize=5,@page=2

[zjcxc]

更多的参考我的贴子:

查询第X页,每页Y条记录
http://expert.csdn.net/Expert/topic/2365/2365596.xml?temp=.4677393

[zjcxc]

--完全按照楼主的效果:

--创建数据测试环境
create table #t_mytab(name varchar(2),ID int,T1 int,T2 int)
insert into #t_mytab
select 'A',1,11,12
union all select 'A',2,12,13
union all select 'A',3,13,14
union all select 'B',1,10,11
union all select 'B',2,12,14
union all select 'C',1,11,12
union all select 'C',2,10,12
union all select 'C',3,12,14

--数据处理
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
select @s1='',@s2='',@s3=''

select @s1='(select ID,name,T1,T2 from #t_mytab where name='''+min(name)+''')a'
from #t_mytab

select @s3=@s3+','+name+'.name,'+name+'.T1,'+name+'.T2'
,@s2=@s2+' full join(select ID,name,T1,T2 from #t_mytab where name='''+name
+''')'+name+' on a.id='+name+'.id'
from(select distinct name from #t_mytab where name<>(select min(name) from #t_mytab)) a

exec('select a.*'+@s3+' from'+@s1+@s2)
go

--删除数据测试环境
drop table #t_mytab


/*--测试结果:

ID name T1 T2 name T1 T2 name T1 T2
---- ---- ------- ------- ---- ------- ------- ---- ----------- -----------
1 A 11 12 B 10 11 C 11 12
2 A 12 13 B 12 14 C 10 12
3 A 13 14 NULL NULL NULL C 12 14

--*/

[zjcxc]

不好意思,发错地方.

[carolbaby]

在mail表中查询第1001－2000条记录。

查询成本31.47%
select top 1000 * from mail where id not in (select top 1000 id from mail)

查询成本35.24%
select top 1000 * from (select top 2000 * from mail order by id)a order by id desc

查询成本33.28%
select * from mail
where id
between
(select max(id) from (select top 1000 id from mail order by id) a)
and
(select max(id) from (select top 2000 id from mail order by id) b)

是不是说明第一种写法最好？可是我记得听说是第二种写法最好，也是微软Sql-server培训中给的标准解法。

[TonyTonyQ]

因为我只能用一条语句，所以采用了gmlxf兄的方法。