sql 分页
1. top分页法
select top pageSize from tb
where ID not in (select top pageSize*pageIndex from tb)
select top pageSize from tb
where ID > select Max(ID) from (select top pageSize*pageIndex from tb) as t
2. RowNum()
select * from (select row_number() over (order by ID) as rowNumber,* from tb) temp where rowNumber between pageSize*(pageIndex-1)+1 and pageSize*pageIndex
--1.myself
------------------------------------------------------------
USE HR;
GO
IF EXISTS(SELECT * FROM sys.objects WHERE NAME='usp_PagingProc')
DROP PROCEDURE usp_PagingProc
GO
--分页存储过程
Create PROC [dbo].[usp_PagingProc]
(
@searchColumn nvarchar(500) = '', --要查的列名
@tableName nvarchar(500) = '', --所查表名，包括left/right/inner join表
@condition nvarchar(500) = '', --条件
@sortColumn nvarchar(500) = '', --排序列
@isDesc bit = 0, --是否降序,1代表降序,0默认代表升序
@pageIndex int = 1, --当前页
@pageSize int = 10, --每页记录数
@totalNum int output --总记录数
)
as
declare @sqlSelect nvarchar(2500); --查询语句
declare @totalNumSelect nvarchar(3000); --数据行
declare @totalSelect nvarchar(4000); --总执行语句
DECLARE @orderByStr nvarchar(200); --排序语句
--处理排序
if(LEN(@sortColumn) > 0)
begin
IF(@isDesc = 1)
SET @orderByStr = 'order by '+ @sortColumn +' desc';
ELSE
SET @orderByStr = 'order by '+ @sortColumn +' asc';
end
--拼接查询语句
SET @sqlSelect='SELECT ROW_NUMBER() over(' + @orderByStr + ') as rowIndex,' + @searchColumn
+' FROM ' + @tableName
+ ' WHERE 1 = 1 ' + @condition;
--Print @sqlSelect + char(10);
--总记录数
SET @totalNumSelect='select @total=COUNT(1) FROM (' + @sqlSelect + ') t';
EXECUTE sp_executesql @totalNumSelect,N'@total int output',@totalNum OUTPUT;--查询总行数
PRINT '总记录数: ' + @totalNumSelect + char(10);
IF(@totalNum IS NULL)
SET @totalNum=0;
--总执行
SET @totalSelect='select * FROM (' + @sqlSelect + ') as t where t.rowIndex between '+convert(nvarchar(20), ((@pageIndex-1)*@pageSize+1))+' and '+convert(nvarchar(20),(@pageIndex*@pageSize));
PRINT '总执行: ' + @totalSelect + char(10);
EXEC(@totalSelect);
RETURN @totalNum;
--示例
--原sql查询所有
/*
select c.Id
,i.Name
,c.IndustryID
,c.[Company]
,c.[Office]
,c.[Salary]
,c.[Hiring]
,c.[Area]
,c.[SuccessNumber]
,c.[RecomDay]
,c.[RecomNumber]
,c.[SuccessDay]
,c.[Title]
,c.[Content]
from [dbo].[Case] c
left join Industry i on c.IndustryID = i.Id
where 1=1 and c.Id >= 3
go
*/
--执行存储过程
/*
declare @totalNum int; --总记录数
EXEC [dbo].[usp_PagingProc] 'c.Id,i.Name,c.IndustryID,c.[Company],c.[Office],c.[Salary],c.[Hiring],c.[Area],c.[SuccessNumber],c.[RecomDay],c.[RecomNumber],c.[SuccessDay],c.[Title],c.[Content]',
'[dbo].[Case] c left join Industry i on c.IndustryID = i.Id',
' and c.Id >= 3','c.Id',0,1,10,@totalNum output;
select @totalNum as 总记录数; --输出值: 总记录数
*/
go
------------------------------------------------------------
--2. 摘自网上
CREATE PROCEDURE [dbo].[SP_EDM_PageAll]
@TableName varchar(350), --表名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderField varchar(5000), --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int = 1, --指定当前为第几页
@distinct VARCHAR(50)=NULL, --去除重复值，注意只能是一个字段
@top INT=NULL --查询TOP,不传为全部
AS
BEGIN
Declare @sql nvarchar(4000);
Declare @totalRecord int;
DECLARE @totalPage INT;
--计算总记录数
IF (@distinct IS NULL OR @distinct='')
BEGIN
IF (@SqlWhere='' OR @sqlWhere IS NULL)
SET @sql = 'select @totalRecord = count(1) from ' + @TableName
ELSE
SET @sql = 'select @totalRecord = count(1) from ' + @TableName + ' where ' + @sqlWhere
END
ELSE
BEGIN
IF (@SqlWhere='' OR @sqlWhere IS NULL)
SET @sql = 'select @totalRecord = count(distinct ' + @distinct + ') from ' + @TableName
ELSE
SET @sql = 'select @totalRecord = count(distinct ' + @distinct + ') from ' + @TableName + ' where ' + @sqlWhere
END
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
IF(@top>0)
BEGIN
--指定TOP 记录
SET @Fields= 'top ' + CAST(@top AS VARCHAR(20)) + ' ' + @Fields;
--如果总记录数超过TOP数,设总记录数为TOP数
IF(@totalRecord>@top)
SET @totalRecord=@top
END
--计算总页数
SELECT @totalPage=CEILING((@totalRecord+0.0)/@PageSize)
SELECT @totalRecord AS 'fldtotalRecord',@totalPage AS 'fldTotalPage'
IF (@distinct IS NULL OR @distinct='')
BEGIN
IF (@SqlWhere='' or @sqlWhere IS NULL)
SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName
ELSE
SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName + ' where ' + @SqlWhere
END
ELSE
BEGIN
IF (@SqlWhere='' or @sqlWhere IS NULL)
SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName
ELSE
SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName + ' where ' + @SqlWhere
SET @sql=@sql + ' GROUP BY ' + @distinct;
END
--处理页数超出范围情况
IF @PageIndex<=0
SET @pageIndex = 1
--处理开始点和结束点
DECLARE @StartRecord INT
DECLARE @EndRecord int
SET @StartRecord = (@pageIndex-1)*@PageSize + 1
SET @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
SET @sql = @sql + ') as tempTable where rowId >=' + CONVERT(VARCHAR(50),@StartRecord) + ' and rowid<= ' + CONVERT(VARCHAR(50),@EndRecord)
print @sql
Exec(@sql)
end
