一般的方法
select Top pageSize *
from T
where SortField NOT IN (select Top pageSize*pagei SortField
from T
order by SortField )
order by SortField例如,每页7条,列印第7页:
select Top 10 *
from T
where SortField NOT IN (select Top 70 SortField
from T
order by SortField )
order by SortField
or CREATE PROCEDURE Get_Customers_By_Page
@CurrentPage int,
@PageSize int,
@TotalRecords int output
asSELECT identity(int,1,1) as id ,* into #TempTable from tableDECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)SELECT
*
FROM
#TempTable
WHERE
ID > @FirstRec
AND
ID < @LastRecSELECT @TotalRecords = COUNT(*) FROM table
或:如果你排序的话,第一次用
select top 100 * from yourtable order by SomeField记住每页的第一个和最后一个记录往后翻页,你用
select top 100 * from yourtable where SomeField > '最后一个记录' order by
SomeField往前翻页,你用
select * from (select top 100 * from yourtable where SomeField < '第一个
记录' order by SomeField desc ) t order by SomeField
select Top pageSize *
from T
where SortField NOT IN (select Top pageSize*pagei SortField
from T
order by SortField )
order by SortField例如,每页7条,列印第7页:
select Top 10 *
from T
where SortField NOT IN (select Top 70 SortField
from T
order by SortField )
order by SortField
or CREATE PROCEDURE Get_Customers_By_Page
@CurrentPage int,
@PageSize int,
@TotalRecords int output
asSELECT identity(int,1,1) as id ,* into #TempTable from tableDECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)SELECT
*
FROM
#TempTable
WHERE
ID > @FirstRec
AND
ID < @LastRecSELECT @TotalRecords = COUNT(*) FROM table
或:如果你排序的话,第一次用
select top 100 * from yourtable order by SomeField记住每页的第一个和最后一个记录往后翻页,你用
select top 100 * from yourtable where SomeField > '最后一个记录' order by
SomeField往前翻页,你用
select * from (select top 100 * from yourtable where SomeField < '第一个
记录' order by SomeField desc ) t order by SomeField
用存储过程实现的分页程序
*/
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
create PROCEDURE up_custompage
@vc_order_column_name_main varchar(100),
@vc_order_column_name varchar(100),
@vc_select_column_list varchar(100),
@vc_select_table_list varchar(100),
@vc_condition varchar(100),
@page_size int,
@current_page int,
@total1 int output
--@vc_order_column_name_main :表的主键,排序列的列名,而且该列必须得在输出列表中,;
-- @vc_order_column_name :表要排序列的列名,只能按一列排序,而且该列必须得在输出列表中;
--1和2不能为同一字段
-- @vc_select_column_list :返回列的列名列表,可以为多个列;
-- @vc_select_table_list:要查询的表名及关联表的连接;
--@vc_condition:查询条件的字符串,必须要有查询条列,否则会抛出异常;
--@page_size:每页显示记录的条数;
--@current_page:当前页的页码;
--@total1:所有符合条件的记录的总数。
AS
declare @temp1 varchar(500),@temp2 nvarchar(500),@page_total int,@last_page int
set @temp2=N'select @total2=count(*) from ' + @vc_select_table_list + ' where ' + @vc_condition
exec sp_executesql @temp2,N' @total2 int output ',@total1 output
if @total1>0
begin
set @page_total=(@total1+@page_size-1)/@page_size
if @current_page<@page_total or @total1%@page_size=0
set @temp1='select top ' + cast(@page_size as varchar(4)) + ' * from
(select top ' + cast(@page_size as varchar(4)) + ' ' + @vc_select_column_list + ' from ' + @vc_select_table_list +' where ' + @vc_order_column_name_main
+ ' in (select top ' + cast(@page_size*@current_page as varchar(10)) + ' ' + @vc_order_column_name_main + ' from ' +
@vc_select_table_list + ' where '+ @vc_condition + ' order by ' +
@vc_order_column_name +','+@vc_order_column_name_main+ ') order by ' + @vc_order_column_name +' desc ,' + @vc_order_column_name_main+' desc) as temp1 order by ' + @vc_order_column_name +','+@vc_order_column_name_main
else
begin
set @last_page=@total1%@page_size
set @temp1='select top ' + cast(@last_page as varchar(4)) + ' * from
(select top ' + cast(@last_page as varchar(4)) + ' ' + @vc_select_column_list + ' from ' + @vc_select_table_list +' where ' + @vc_order_column_name_main
+ ' in (select top ' + cast(@total1 as varchar(10)) + ' ' + @vc_order_column_name_main + ' from ' +
@vc_select_table_list + ' where '+ @vc_condition + ' order by ' +
@vc_order_column_name +','+@vc_order_column_name_main+ ') order by ' + @vc_order_column_name + ' desc ,'+@vc_order_column_name_main+ ' desc) as temp1 order by ' + @vc_order_column_name +','+@vc_order_column_name_main
end
print @temp1
exec(@temp1)
end
else
return
还想听一下zjcxc的ADO的方法,我在试ADO时,recordset可能被其他的查询请求给刷新,从而失败。