如果查询结果中有主键(或不重复的字段),可以用类似下面的语句:select top 50 * from(查询语句) a where 主键 not in(select top 50 主键 from(查询语句) a) 如果查询结果不满足上面的要求,就不可能用一个SQL搞定.另外,即使满足上面的要求, 效率也不太好.
对于不能用上面语句处理的情况,用类似下面的存储过程来实现/*--用存储过程实现的分页程序 显示指定查询结果的第X页 此存储过程针对无主键的情况,使用临时表的方法 如果有主键,不推荐此方法--邹建 2003.09*//*--调用示例 exec p_show 'select * from xzkh_new..地区资料' exec p_show 'select * from xzkh_new..地区资料',5,2,'地区编号,地区名称,助记码','地区编号' --*/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] GOCREATE Proc p_show @QueryStr nvarchar(1000), --查询语句,如果查询表,用:select * from 表 @PageSize int=10, --每页的大小(行数) @PageCurrent int=1, --要显示的页 @FdShow nvarchar (1000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段 @FdOrder nvarchar (1000)='' --排序字段列表 as declare @FdName nvarchar(50) --标识列名 ,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号 select @FdName='[ID_'+cast(newid() as varchar(40))+']' ,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20)) ,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20)) ,@FdShow=case isnull(@FdShow,'') when '' then '*' else @FdShow end ,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder endexec('select '+@FdName+'=identity(int,0,1),'+@FdShow+' into #tb from('+@QueryStr+') a '+@FdOrder+' select '+@FdShow+' from #tb where '+@FdName+' between ' +@Id1+' and '+@Id2 ) GO
Select top 50 * from ( Select top 100 * from table order by FieldName) bb order by FieldName desc
选50-100之间的记录 select top 50 * from ( select top 100 * from table order by FieldName desc) bb order by FieldName
YUKON:$IDENTITY 方便地自动建立查询语句例如下面语句很容易用程序建立:select * from Employees where $Identity in ( select top 2 $Identity from Employees where $Identity in ( select top 4 $Identity from Employees where EmployeeID!=8 order by FirstName ) order by FirstName desc ) order by FirstName
我同意 马可&不做技术高手的写法!!
select ids=identity(int,1,1),l.* into #table2 from table as l select (要显示的字段) from #table2 where ids between 50 and 100
select ids=identity(int,1,1),l.* into #table2 from table as l order by FieldName select (要显示的字段) from #table2 where ids between 50 and 100
此存储过程针对无主键的情况,使用临时表的方法
如果有主键,不推荐此方法--邹建 2003.09*//*--调用示例
exec p_show 'select * from xzkh_new..地区资料' exec p_show 'select * from xzkh_new..地区资料',5,2,'地区编号,地区名称,助记码','地区编号'
--*/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]
GOCREATE Proc p_show
@QueryStr nvarchar(1000), --查询语句,如果查询表,用:select * from 表
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (1000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(50) --标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
,@FdShow=case isnull(@FdShow,'') when '' then '*' else @FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder endexec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from('+@QueryStr+') a '+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
GO
Select top 50 * from (
Select top 100 * from table order by FieldName) bb order by FieldName desc
select top 50 * from (
select top 100 * from table order by FieldName desc) bb order by FieldName
select top 2 $Identity from Employees where $Identity in (
select top 4 $Identity from Employees
where EmployeeID!=8
order by FirstName
)
order by FirstName desc
)
order by FirstName
select (要显示的字段) from #table2 where ids between 50 and 100
select (要显示的字段) from #table2 where ids between 50 and 100
letsflytogether(恨!不能拥有天下所有的财富,然后
你俩的答案不符合要求,我的意思要取出100条中的30-60,不是后50条。LevinForum(forum.lostinet.com) 写的本人看不懂,可能是水平有限。bixuehua(bixuehua) 虽然可以实现但是要写一个存储过程,我想要一条SQL实现。最后我决定采用zjcxc(邹建)的写法,虽然不知道效率怎么样。