查询

[awjx]

查询一张表，条件是：指定查询的起始行（@row）,每页返回的行数（@page）。
即：从第@row行开始，每页返回@page行。请问该怎样写？

[hjb111]

create proc p_record_row @row int,@page int
as
select identity(int,1,1) as idd,* into #t from yourtable
select * from #t where idd between @row and @row+@page

[txlicenhe]

select identity(int,1,1) as id,* into #tmp from yourtable
select * from #tmp where id between @row and @row+@page
drop table #tmp

[txlicenhe]

Select identity(int,1,1) as id,* into #tmp from yourTable
select * from #tmp where id between @row and @row + @page
drop table #tmp

[CrazyFor]

查询N-M条记录。
select IDENTITY(int,1,1) as iid,* into #temptable from yourtable
select top M-N * from #temptable where iid>=N

OR:

select top M-N * from yourTable where id not in(select top N-1 id from table)
ID为表具有唯一值的任何字段

[zjcxc]

/*
用存储过程实现的分页程序
*/
CREATE procedure Department_pagination
@SelectStr nvarchar(1000),
@ColumnStr nvarchar (1000),
@OrderStr nvarchar (1000),
@CurrentPage int,
@PageCount int
as
declare @TimeName nvarchar(25)
declare @TableStr nvarchar(1000)

select @TimeName = convert(nvarchar(23), getdate(), 121)
set @TimeName = REPLACE(@TimeName, '.', '')
set @TimeName = REPLACE(@TimeName, ':', '')
set @TimeName = REPLACE(@TimeName, '-', '')
set @TimeName = REPLACE(@TimeName, ' ', '')

select @TableStr='create table ##Tab' + @TimeName + '(wb int identity,'
exec(@TableStr+@ColumnStr+')')
exec('insert into ##Tab' + @TimeName + ' ' + @SelectStr + ' order by ' + @OrderStr)
exec('select * from ##Tab' + @TimeName + ' where wb between ((' + @CurrentPage + '-1)*' + @PageCount + '+1) and ' + @CurrentPage + '*' + @PageCount + ' order by wb')
exec('drop table ##Tab' + @TimeName)
GO



参数1：select语句。2：字段列表。3：排序字段。4：当前页。5每页数目