一.sql分页:先分页后多表连接查询
declare @row int; select @row=COUNT(*) from BS_Subject where 1=1; SELECT TOP 10 @row as Record, a.*,c.ClassName FROM BS_Subject a left join BS_Class c on a.ClassID=c.ClassID WHERE 1=1 and a.ID not IN(SELECT TOP 1000000 ID FROM BS_Subject where 1=1 ORDER BY ID desc)ORDER BY a.ID desc
虽然这样可以快速查询出来，但是在程序里不好调用。
二.存储过程分页：单表，多表，多字段排序，总数
create PROCEDURE [dbo].[usp_PagingLarge] @TableNames VARCHAR(200), --表名，可以是多个表，但不能用别名 @PrimaryKey VARCHAR(100), --主键，可以为空，但@Order为空时该值不能为空 @Fields VARCHAR(200), --要取出的字段，可以是多个表的字段，可以为空，为空表示select * @PageSize INT, --每页记录数 @CurrentPage INT, --当前页，0表示第1页 @Filter VARCHAR(200) = '', --条件，可以为空，不用填 where @Group VARCHAR(200) = '', --分组依据，可以为空，不用填 group by @Order VARCHAR(200) = '', --排序，可以为空，为空默认按主键升序排列，不用填 order by @RecordCount int --返回总页数 1只返回总数，0只返回当前页数据，2返回总数并返回当前页数据 AS BEGIN DECLARE @SortColumn VARCHAR(200) DECLARE @Operator CHAR(2) DECLARE @SortTable VARCHAR(200) DECLARE @SortName VARCHAR(200) declare @strSQL varchar(500) if @RecordCount=1 begin IF @Filter = '' begin set @strSQL = 'select count(*) as Total from ' + @TableNames end else begin set @strSQL = 'select count(*) as Total from ' + @TableNames + ' where '+@Filter end exec (@strSQL); end else begin IF @Fields = '' SET @Fields = '*' IF @Filter = '' SET @Filter = 'WHERE 1=1' ELSE SET @Filter = 'WHERE ' + @Filter IF @Group <>'' SET @Group = 'GROUP BY ' + @Group IF @Order <> '' BEGIN DECLARE @pos1 INT, @pos2 INT SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC') IF CHARINDEX(' DESC', @Order) > 0 IF CHARINDEX(' ASC', @Order) > 0 BEGIN IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order) SET @Operator = '<=' ELSE SET @Operator = '>=' END ELSE SET @Operator = '<=' ELSE SET @Operator = '>=' SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '') SET @pos1 = CHARINDEX(',', @SortColumn) IF @pos1 > 0 SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1) SET @pos2 = CHARINDEX('.', @SortColumn) IF @pos2 > 0 BEGIN SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1) IF @pos1 > 0 SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1) ELSE SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2) END ELSE BEGIN SET @SortTable = @TableNames SET @SortName = @SortColumn END END ELSE BEGIN SET @SortColumn = @PrimaryKey SET @SortTable = @TableNames SET @SortName = @SortColumn SET @Order = @SortColumn SET @Operator = '>=' END DECLARE @type varchar(50) DECLARE @prec int SELECT @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName IF CHARINDEX('char', @type) > 0 SET @type = @type + '(' + CAST(@prec AS varchar) + ')' DECLARE @TopRows INT SET @TopRows = @PageSize * @CurrentPage + 1 print @TopRows print @Operator if @RecordCount=2 begin IF @Filter = '' begin set @strSQL = 'declare @PageRecordNumber int;select @PageRecordNumber=count(*) from ' + @TableNames+';' end else begin set @strSQL = 'declare @PageRecordNumber int;select @PageRecordNumber=count(*) from ' + @TableNames+' '+@Filter+';' end EXEC(@strSQL+' DECLARE @SortColumnBegin ' + @type + ' SET ROWCOUNT ' + @TopRows + ' SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + ' SET ROWCOUNT ' + @PageSize + ' SELECT ' + @Fields + ',@PageRecordNumber as Page_RecordNumber FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + ' ') end else begin EXEC(' DECLARE @SortColumnBegin ' + @type + ' SET ROWCOUNT ' + @TopRows + ' SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + ' SET ROWCOUNT ' + @PageSize + ' SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + ' ') end end END
调用
declare @ct datetime set @ct = getdate() EXEC usp_PagingLarge 'BS_Acode left join BS_Class on BS_Acode.ClassID=BS_Class.ClassID', 'BS_Acode.ID','BS_Acode.*,BS_Class.ClassName',10,100,'','', 'BS_Acode.ID desc,BS_Acode.CreateDate desc',2 select datediff(ms, @ct ,getdate()) as '查询时间（毫秒）'
搜索了好多分页的存储过程，这个是挺好用的。可以支持单表、多表的查询，还可以排序（多字段排序），也可以返回总数，效率也好。之前用的一个，在前面的页数的时候查询很快，当到中后的页面的时候非常慢。
证明设置索引的必要性，我之前并相信设置这玩意儿会带来什么，关键是自己真不知道怎么看。其实是自己愚笨，不懂用了之后就说他没用。另外，非常确定ordery by真的能不用就用，经过尝试加上非常影响效率。
好友的总结，我收下了：
1.表的主键、外键必须有索引
2.数据量超过300的表应该有索引
3.经常与其他表进行连接的表，在连接字段上应该建立索引
4.经常出现在where字句中的字段，特别是大表的字段，应该建立索引
5.索引应该建在选择性高的字段上
6.索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建立索引
7.复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替
分页注意点：分页的时候一定要确保每一条数据都尤其自己的位置（顺序），比如按照热度排序的时候，就得order by 热度，发表时间，主键
这样就可以避免了在相同热度，相同发表时间里，再按照主键排序，就能将每个数据确定他的位置了。
-------------好多东西都是需要慢慢完善的,再自己没有发现的时候,它都是最好的-------------------
---上面的存储过程分页在分组查询的时候,不能order by分组字段。修改了一下，现在可以了
ALTER PROCEDURE [dbo].[usp_PagingLarge] @TableNames VARCHAR(500), --表名，可以是多个表，但不能用别名 @PrimaryKey VARCHAR(500), --主键，可以为空，但@Order为空时该值不能为空 @Fields VARCHAR(500), --要取出的字段，可以是多个表的字段，可以为空，为空表示select * @PageSize INT, --每页记录数 @CurrentPage INT, --当前页，0表示第1页 @Filter VARCHAR(500) = '', --条件，可以为空，不用填 where @Group VARCHAR(500) = '', --分组依据，可以为空，不用填 group by @Order VARCHAR(500) = '', --排序，可以为空，为空默认按主键升序排列，不用填 order by @RecordCount int --返回总页数 1只返回总数，0只返回当前页数据，2返回总数并返回当前页数据 AS BEGIN DECLARE @SortColumn VARCHAR(200) DECLARE @Operator CHAR(2) DECLARE @SortTable VARCHAR(200) DECLARE @SortName VARCHAR(200) declare @strSQL varchar(500) if @RecordCount=1 begin IF @Filter = '' begin set @strSQL = 'select count(*) as Total from ' + @TableNames end else begin set @strSQL = 'select count(*) as Total from ' + @TableNames + ' where '+@Filter end exec (@strSQL); end else begin IF @Fields = '' SET @Fields = '*' IF @Filter = '' begin IF @Group <>'' begin SET @Filter = 'having 1=1' end else begin SET @Filter = 'WHERE 1=1' end end ELSE begin IF @Group <>'' begin SET @Filter = 'having '+@Filter end else begin SET @Filter = 'WHERE ' + @Filter end end IF @Group <>'' SET @Group = 'GROUP BY ' + @Group IF @Order <> '' BEGIN DECLARE @pos1 INT, @pos2 INT SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC') IF CHARINDEX(' DESC', @Order) > 0 IF CHARINDEX(' ASC', @Order) > 0 BEGIN IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order) SET @Operator = '<=' ELSE SET @Operator = '>=' END ELSE SET @Operator = '<=' ELSE SET @Operator = '>=' SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '') SET @pos1 = CHARINDEX(',', @SortColumn) IF @pos1 > 0 SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1) SET @pos2 = CHARINDEX('.', @SortColumn) IF @pos2 > 0 BEGIN SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1) IF @pos1 > 0 SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1) ELSE SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2) END ELSE BEGIN SET @SortTable = @TableNames SET @SortName = @SortColumn END END ELSE BEGIN SET @SortColumn = @PrimaryKey SET @SortTable = @TableNames SET @SortName = @SortColumn SET @Order = @SortColumn SET @Operator = '>=' END DECLARE @type varchar(50) DECLARE @prec int SELECT @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName IF CHARINDEX('char', @type) > 0 begin SET @type = @type + '(' + CAST(@prec AS varchar) + ')' end if @type is null and @Group<>'' begin set @type='int' end DECLARE @TopRows INT SET @TopRows = @PageSize * @CurrentPage + 1 if @RecordCount=2 begin IF @Filter = '' begin set @strSQL = 'declare @PageRecordNumber int;select @PageRecordNumber=count(*) from ' + @TableNames+';' end else begin set @strSQL = 'declare @PageRecordNumber int;select @PageRecordNumber=count(*) from ' + @TableNames+' '+replace(@Filter,'having','where')+';' end IF @Group <>'' begin --把分组和不分组的查询分开，查询条件和位置不一样 EXEC(@strSQL+' DECLARE @SortColumnBegin ' + @type + ' SET ROWCOUNT ' + @TopRows + ' SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Group+' '+@Filter + ' ORDER BY ' + @Order + ' SET ROWCOUNT ' + @PageSize + ' SELECT ' + @Fields + ',@PageRecordNumber as Page_RecordNumber FROM ' + @TableNames + ' ' + @Group+' '+@Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + ' ORDER BY ' + @Order + ' ') end else begin EXEC(@strSQL+' DECLARE @SortColumnBegin ' + @type + ' SET ROWCOUNT ' + @TopRows + ' SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + ' SET ROWCOUNT ' + @PageSize + ' SELECT ' + @Fields + ',@PageRecordNumber as Page_RecordNumber FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + ' ') end end else begin IF @Group <>'' begin --把分组和不分组的查询分开，查询条件和位置不一样 EXEC(' DECLARE @SortColumnBegin ' + @type + ' SET ROWCOUNT ' + @TopRows + ' SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Group +' '+@Filter+ ' ORDER BY ' + @Order + ' SET ROWCOUNT ' + @PageSize + ' SELECT ' + @Fields + ' FROM ' + @TableNames +' '+ @Group+' '+ @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + ' ORDER BY ' + @Order + ' ') end else begin EXEC(' DECLARE @SortColumnBegin ' + @type + ' SET ROWCOUNT ' + @TopRows + ' SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + ' SET ROWCOUNT ' + @PageSize + ' SELECT ' + @Fields + ' FROM ' + @TableNames +' '+ @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + ' ') end end end END
EXEC usp_PagingLarge @TableNames = N'User_ExchangeGoods left join User_ExchangeOrder on User_ExchangeGoods.GID=User_ExchangeOrder.GoodsID', @PrimaryKey = N'User_ExchangeGoods.GID', @Fields = N'User_ExchangeGoods.GID,User_ExchangeGoods.GoodsName,User_ExchangeGoods.GoodsPrice,User_ExchangeGoods.ExchangeIntegral,cast(User_ExchangeGoods.GoodsPictures as varchar(max)) as GoodsPictures', @PageSize = 10, @CurrentPage = 0, @Filter = '1=1', @Group = N'User_ExchangeGoods.GID,User_ExchangeGoods.GoodsName,User_ExchangeGoods.GoodsPrice,User_ExchangeGoods.ExchangeIntegral,cast(User_ExchangeGoods.GoodsPictures as varchar(max))', @Order = 'COUNT(User_ExchangeOrder.GoodsID) desc', @RecordCount = 0
上面，GoodPictures类型是text，会报错“不能比较或排序 text、ntext 和 image 数据类型，除非使用 IS NULL 或 LIKE 运算符。”，转换一下即可。
但是分页存储过程当有两个字段排序时，第二个排序无效。
——————————————————————————
事实上上面的存储过程并不是通用的，个别情况的时候还是得自己写一个分页sql。
SELECT TOP 页大小 * FROM 表 WHERE (ID NOT IN (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id)) ORDER BY ID