以前项目中是用的假分页,Sql语句是通过xml文件传递过来,文件量过多,不好再改Sql语句。现在求思路或直接方法更好。假如我现在是有一个 查询语句。这个查询结果是不固定的(各种可能都有,也许没有自动增长列)。现在需要请教的是 例: select aa,bb,cc from student 我现在要取这个查询结果的 第5到10条。总体的意思就是在 (select aa,bb,cc from student)查询语句不变的情况下。对这个表的结果集进行抽取我想要的一部分。知道三个变化的参数 sql语句:同上 ,startNum 开始行, endNum 结束行。想要结果。不知道能否做到。如果不能有别的思路也可以告之,谢啦。
from (select aa,bb,cc,row=row_number()over(order by (select 1)) from student)t
where row betwen 5 and 10
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum,
newsid, topic, ntime, hits
FROM news) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY newsid DESC aspx里面只需给SQL传入pageid和条数即可。 CSDN上还有个存储过程实现分页的代码:
ALTER PROCEDURE news_Showlist
(
@tblName varchar(255), -- 表名
@strGetFields varchar(1000), -- 需要返回的列
@fldName varchar(255), -- 排序的字段名
@PageSize int , -- 页尺寸
@PageIndex int , -- 页码
@strWhere varchar(1500), -- 查询条件(注意: 不要加where)
@Sort varchar(255) --排序的方法)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型if @Sort = 'desc'
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @fldName +' desc'
--如果@OrderType不是,就执行降序,这句很重要!
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @fldName +' asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
begin
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName
+ ' where ' + @strWhere + ' ' + @strOrder
end
else
begin
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName
+ ' '+ @strOrder
end
--如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('+ @fldName + ') from
(select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from '
+ @tblName + '' + @strOrder + ') as tblTmp)'+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('+ @fldName + ') from
(select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from '
+ @tblName + ' where ' + @strWhere + ' '+ @strOrder + ') as tblTmp)
and ' + @strWhere + ' ' + @strOrder
end
exec (@strSQL)
RETURN
select top (n-m+1) * 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
(
select aa,bb,cc ,row_number() over(order by a, b, c) px from student
) t
where px between 5 and 10
ALTER PROCEDURE [dbo].[UP_Page_Get]
@pageIndex varchar(10),--页数索引
@pageSize varchar(10),--页数大小
@pk varchar(64),--主键
@where varchar(512),--查询条件
@tableName varchar(256),--查询的表名
@select varchar(512),--查询的列
@order varchar(128)--排序
AS
declare @sql varchar(8000)
if(@where='')
begin
set @sql = 'select top('+@pageSize+' ) '+@select+' from'+@tableName
set @sql = @sql+' where '+@pk+' not in (select top ('+@pageIndex+') '+@pk+' from '+@tableName+' order by '+@order+' )'
set @sql = @sql+' order by '+@order
print(@sql)
exec(@sql)
end
else
begin
set @sql='select top ('+@pageSize+' ) '+@select+'from '+@tableName
set @sql=@sql+' where '+@where+'and '+@pk+' not in (select top ('+@pageIndex+') '+@pk+' from '+@tableName+' where '+@where+' order by '+@order+' )'
set @sql=@sql+' order by '+@order
print(@sql)
exec(@sql)
end