null - 程序员宅基地

几大高效分页存储过程汇总-程序员宅基地

技术标签： C# C++ C Go SQL

select * from city

insert into [city](cityid,[city],father) select (cityid,[city],father) from [city]

select count(1) from area

create proc aa
as
begin
declare @int int
set @int=0
while @int<10
begin
insert into area(areaid,area,father) select areaid,area,father from area
set @int=@int+1
end
end
go

exec aa
DECLARE @RC int
DECLARE @sql nvarchar(4000)
DECLARE @page int
DECLARE @pageSize int
DECLARE @pageCount int
DECLARE @recordCount int
DECLARE @SearchTime int
---— 设置参数值
EXEC @RC = [pubs].[dbo].[p_splitpage] 'select * from area', 1000, 100, @pageCount OUTPUT , @recordCount OUTPUT , @SearchTime OUTPUT

CREATE procedure p_splitpage
@sql nvarchar(4000),--要执行的sql语句
@page int=1, --要显示的页码
@pageSize int,--每页的大小
@pageCount int=0 out,--总页数
@recordCount int=0 out,--总记录数
@SearchTime int=0 out --计算耗时
as
declare @usetime datetime
set @usetime=getdate()
set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordCount = @pageCount
select @pagecount=ceiling(1.0*@pagecount/@pagesize)
,@page=(@page-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@page,@pagesize
exec sp_cursorclose @p1
set @SearchTime=datediff(ms,@usetime,getdate())
print @SearchTime

DECLARE @RC int
DECLARE @sql nvarchar(4000)
DECLARE @page int
DECLARE @pageSize int
DECLARE @pageCount int
DECLARE @recordCount int
DECLARE @SearchTime int
--— 设置参数值
EXEC @RC = [pubs].[dbo].[p_splitpage] 'select * from area', 1, 100, @pageCount OUTPUT , @recordCount OUTPUT , @SearchTime OUTPUT

DECLARE @RC int
DECLARE @Tables varchar(1000)
DECLARE @PrimaryKey varchar(100)
DECLARE @Sort varchar(200)
DECLARE @CurrentPage int
DECLARE @PageSize int
DECLARE @Fields varchar(1000)
DECLARE @Filter varchar(1000)
DECLARE @Group varchar(1000)
--— 设置参数值
EXEC [Pages] 'companyinfo', 'c_id', 'addtime desc,c_id desc', 1093, 100, '*', '', ''

alter PROCEDURE Pages
/*
***************************************************************
** 千万数量级分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句，不带Where
7.Group :Group语句,不带Group By
效果演示：http://www.cn5135.com/_App/Enterprise/QueryResult.aspx
***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL
)
AS
/*默认排序*/
IF @Sort IS NULL or @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/*设定排序语句.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
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 @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
/*筛选以及分组语句.*/

IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' Where ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*执行查询语句*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
Select @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
Select ' + @Fields + ' FROM ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + '
'
)
GO

exec p_show 'select * from area',100,100,'*',''

CREATE Proc p_show
@QueryStr nvarchar(1000), --查询语句,如果查询表,用:select * from 表
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (1000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(50) --标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
,@FdShow=case isnull(@FdShow,'') when '' then '*' else @FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end

exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from('+@QueryStr+') a '+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
GO

create index greadiddesc on companyinfo(GenderID desc)

select * from companyinfo where sort='397' and companyname like '%浙江黄岩%' order by addtime desc

EXEC pages 'companyinfo','c_id','*',100,1091,' ','','c_id desc, addtime desc'

create index areaiddesc on area (id desc)
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
alter PROCEDURE Pages
@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
AS
BEGIN
DECLARE @SortColumn VARCHAR(200)
DECLARE @Operator CHAR(2)
DECLARE @SortTable VARCHAR(200)
DECLARE @SortName VARCHAR(200)
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
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

select top 10 * from companyinfo where ltrim(dealtype)='生产型'

select count(1) from companyinfo where sort='397'

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: David.Yan
-- Create date: 2007.12.17
-- Description:
/*
高效通用分页存储过程(双向检索)
敬告：适用于单一主键或存在唯一值列的表或视图
ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围
*/
-- =============================================

--------------------------------------------------------------------------------------------------------------
v--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
DECLARE @RC int
DECLARE @TableName varchar(200)
DECLARE @FieldList varchar(2000)
DECLARE @PrimaryKey varchar(100)
DECLARE @Where varchar(2000)
DECLARE @Order varchar(1000)
DECLARE @SortType int
DECLARE @RecorderCount int
DECLARE @PageSize int
DECLARE @PageIndex int
DECLARE @TotalCount int
DECLARE @TotalPageCount int
--— 设置参数值
EXEC @RC = [pubs].[dbo].[P_viewPage] 'area', '*', 'id', '', 'id desc', 3, 100, 1000, @TotalCount OUTPUT , @TotalPageCount OUTPUT

alter PROC P_viewPage
-- Add the parameters for the stored procedure here
@TableName VARCHAR(200), --表名
@FieldList VARCHAR(2000), --显示列名，如果是全部字段则为*
@PrimaryKey VARCHAR(100), --单一主键或唯一值键
@Where VARCHAR(2000), --查询条件不含'where'字符，如id>10 and len(userid)>9
@Order VARCHAR(1000), --排序不含'order by'字符，如id asc,userid desc，必须指定asc或desc
--注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
@SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
--@RecorderCount INT, --记录总数 0:会返回总记录
@PageSize INT, --每页输出的记录数
@PageIndex INT, --当前页数
@TotalCount INT OUTPUT, --记返回总记录
@TotalPageCount INT OUTPUT --返回总页数
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0
SET @Order = RTRIM(LTRIM(@Order))
SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))
SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')
WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0
BEGIN
SET @Order = REPLACE(@Order,', ',',')
SET @Order = REPLACE(@Order,' ,',',')
END
IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = ''
OR ISNULL(@PrimaryKey,'') = ''
OR @SortType < 1 OR @SortType >3
OR @PageSize < 0 OR @PageIndex < 0--@RecorderCount < 0 OR

BEGIN
PRINT('ERR_00参数错误')
RETURN
END
IF @SortType = 3
BEGIN
IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')
BEGIN
PRINT('ERR_02排序错误') RETURN END
END
DECLARE @new_where1 VARCHAR(1000)
DECLARE @new_where2 VARCHAR(1000)
DECLARE @new_order1 VARCHAR(1000)
DECLARE @new_order2 VARCHAR(1000)
DECLARE @new_order3 VARCHAR(1000)
DECLARE @Sql VARCHAR(8000)
DECLARE @SqlCount NVARCHAR(4000)
IF ISNULL(@where,'') = ''
BEGIN
SET @new_where1 = ' '
SET @new_where2 = ' WHERE '
END
ELSE
BEGIN
SET @new_where1 = ' WHERE ' + @where
SET @new_where2 = ' WHERE ' + @where + ' AND '
END
IF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2
BEGIN
IF @SortType = 1
BEGIN
SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC'
SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC'
END
IF @SortType = 2
BEGIN
SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC'
SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC'
END
END
ELSE
BEGIN
SET @new_order1 = ' ORDER BY ' + @Order
END
IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0
BEGIN
SET @new_order1 = ' ORDER BY ' + @Order
SET @new_order2 = @Order + ','
SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')
SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')
SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
IF @FieldList <> '*'
BEGIN
SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')
SET @FieldList = ',' + @FieldList
WHILE CHARINDEX(',',@new_order3)>0
BEGIN
IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0
BEGIN
SET @FieldList =
@FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))
END
SET @new_order3 = SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))
END
SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))
END
END
SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
+ CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1
--IF @RecorderCount = 0
--BEGIN
EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
@TotalCount OUTPUT,@TotalPageCount OUTPUT
--END
--ELSE
--BEGIN
--SELECT @TotalCount = @RecorderCount
--END
IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)
END
IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
IF @PageIndex = 1 --返回第一页数据
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order1
END
IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
+ ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
+ @new_order1
END
END
ELSE
BEGIN
IF @SortType = 1 --仅主键正序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' > '
+ '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '
+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'SELECT TOP ' + STR(@PageSize) + ' '
+ @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' < '
+ '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '
+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
+ ' ) AS TMP ' + @new_order1
END
END
IF @SortType = 2 --仅主键反序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' < '
+ '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '
+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
+' FROM '+ @TableName
+ @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'SELECT TOP ' + STR(@PageSize) + ' '
+ @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' > '
+ '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '
+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
+ ' ) AS TMP ' + @new_order1
END
END
IF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理
BEGIN
IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0
BEGIN
PRINT('ERR_02') RETURN
END
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList
+ ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '
+ @new_order2 + ' ) AS TMP ' + @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ ' SELECT TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList
+ ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
+ @new_order1 + ' ) AS TMP ' + @new_order1
END
END
END
EXEC(@Sql)
GO

DECLARE @RC int
DECLARE @TableName varchar(200)
DECLARE @FieldList varchar(2000)
DECLARE @PrimaryKey varchar(100)
DECLARE @Where varchar(2000)
DECLARE @Order varchar(1000)
DECLARE @SortType int
DECLARE @RecorderCount int
DECLARE @PageSize int
DECLARE @PageIndex int
DECLARE @TotalCount int
DECLARE @TotalPageCount int
--— 设置参数值
EXEC [P_viewPage_A] 'companyinfo', '*', 'c_id', ' sort=''397''', 'c_id desc', 3, 0, 100, 6, @TotalCount OUTPUT , @TotalPageCount OUTPUT
print @TotalCount
print @TotalPageCount

Create PROC P_viewPage_A
/*
nzperfect [no_mIss] 高效通用分页存储过程(双向检索) 2007.5.7 QQ:34813284
敬告：适用于单一主键或存在唯一值列的表或视图
ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围
*/
@TableName VARCHAR(200), --表名
@FieldList VARCHAR(2000), --显示列名，如果是全部字段则为*
@PrimaryKey VARCHAR(100), --单一主键或唯一值键
@Where VARCHAR(2000), --查询条件不含'where'字符，如id>10 and len(userid)>9
@Order VARCHAR(1000), --排序不含'order by'字符，如id asc,userid desc，必须指定asc或desc
--注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
@SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
@RecorderCount INT, --记录总数 0:会返回总记录
@PageSize INT, --每页输出的记录数
@PageIndex INT, --当前页数
@TotalCount INT OUTPUT, --记返回总记录
@TotalPageCount INT OUTPUT --返回总页数
AS
SET NOCOUNT ON
IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0
SET @Order = RTRIM(LTRIM(@Order))
SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))
SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')
WHILE CHARINDEX(', ',@Order) > 0 or CHARINDEX(' ,',@Order) > 0
BEGIN
SET @Order = REPLACE(@Order,', ',',')
SET @Order = REPLACE(@Order,' ,',',')
END
IF ISNULL(@TableName,'') = '' or ISNULL(@FieldList,'') = ''
or ISNULL(@PrimaryKey,'') = ''
or @SortType < 1 or @SortType >3
or @RecorderCount < 0 or @PageSize < 0 or @PageIndex < 0
BEGIN
PRINT('ERR_00')
RETURN
END
IF @SortType = 3
BEGIN
IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')
BEGIN PRINT('ERR_02') RETURN END
END
DECLARE @new_where1 VARCHAR(1000)
DECLARE @new_where2 VARCHAR(1000)
DECLARE @new_order1 VARCHAR(1000)
DECLARE @new_order2 VARCHAR(1000)
DECLARE @new_order3 VARCHAR(1000)
DECLARE @Sql VARCHAR(8000)
DECLARE @SqlCount NVARCHAR(4000)
IF ISNULL(@where,'') = ''
BEGIN
SET @new_where1 = ' '
SET @new_where2 = ' Where '
END
ELSE
BEGIN
SET @new_where1 = ' Where ' + @where
SET @new_where2 = ' Where ' + @where + ' AND '
END
IF ISNULL(@order,'') = '' or @SortType = 1 or @SortType = 2
BEGIN
IF @SortType = 1
BEGIN
SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' ASC'
SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' DESC'
END
IF @SortType = 2
BEGIN
SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' DESC'
SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' ASC'
END
END
ELSE
BEGIN
SET @new_order1 = ' orDER BY ' + @Order
END
IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0
BEGIN
SET @new_order1 = ' orDER BY ' + @Order
SET @new_order2 = @Order + ','
SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')
SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')
SET @new_order2 = ' orDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
IF @FieldList <> '*'
BEGIN
SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')
SET @FieldList = ',' + @FieldList
WHILE CHARINDEX(',',@new_order3)>0
BEGIN
IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0
BEGIN
SET @FieldList =
@FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))
END
SET @new_order3 =
SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))
END
SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))
END
END
SET @SqlCount = 'Select @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
+ CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1
IF @RecorderCount = 0
BEGIN
EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
@TotalCount OUTPUT,@TotalPageCount OUTPUT
END
ELSE
BEGIN
Select @TotalCount = @RecorderCount
END
IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)
END
IF @PageIndex = 1 or @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
IF @PageIndex = 1 --返回第一页数据
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order1
END
IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'Select TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
+ ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
+ @new_order1
END
END
ELSE
BEGIN
IF @SortType = 1 --仅主键正序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' > '
+ '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '
+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'Select TOP ' + STR(@PageSize) + ' '
+ @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' < '
+ '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '
+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
+ ' ) AS TMP ' + @new_order1
END
END
IF @SortType = 2 --仅主键反序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' < '
+ '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '
+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
+' FROM '+ @TableName
+ @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'Select TOP ' + STR(@PageSize) + ' '
+ @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' > '
+ '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '
+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
+ ' ) AS TMP ' + @new_order1
END
END
IF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理
BEGIN
IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0
BEGIN PRINT('ERR_02') RETURN END
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ ' Select TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList
+ ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '
+ @new_order2 + ' ) AS TMP ' + @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ ' Select TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList
+ ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
+ @new_order1 + ' ) AS TMP ' + @new_order1
END
END
END
EXEC(@Sql)
GO

--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------

DECLARE @RC int
DECLARE @PageCurr int
DECLARE @PageSize int
DECLARE @QueryString varchar(5000)
DECLARE @OrderString varchar(5000)
DECLARE @FieldShow varchar(5000)
DECLARE @RecordCount int
--— 设置参数值
EXEC @RC = [pubs].[dbo].[OrderUpendPager] 1000, 100, 'select * from area', 'id desc,father desc', '*', @RecordCount OUTPUT

CREATE PROCEDURE [OrderUpendPager]
@PageCurr int=1, --当前页码
@PageSize int=10, --每页条数
@QueryString varchar(5000), --查询字符串
@OrderString varchar(5000), --排序规则
@FieldShow varchar(5000), --要显示的字段
@RecordCount int output --总记录条数

AS
declare @ViewName varchar(100) --临时查询视图名称
set @ViewName='tempView'
declare @RecordQuery varchar(5000) --记录查询
declare @OrderUpend varchar(5000) --排序规则反转

if(@FieldShow='' or @FieldShow=null)
set @FieldShow='*'
if(@PageCurr=0 or @PageCurr=null)
set @PageCurr=1
if(@PageSize=0 or @PageSize=null)
set @PageSize=10

if(@OrderString='' or @OrderString=null)
begin
print('Err：必须设置排序规则--')
return
end

--反转排序规则开始--
if( charindex(',',@OrderString)=0 )
begin
if(charindex(' desc',@OrderString)=0)
set @OrderUpend=replace(@OrderString,' asc','')+' desc'
else
set @OrderUpend=replace(@OrderString,' desc','')
end
else
begin
set @OrderUpend=''
DECLARE @strSingle varchar(100)
DECLARE @strTemp varchar(1000)
DECLARE @strSpace varchar(10)
set @strSpace=''
set @strTemp=@OrderString

while @strTemp<>''
begin
if(charindex(',',@strTemp)=0)
begin
set @strSingle=@strTemp
set @strTemp=''
end
else
begin
set @strSingle=substring(@strTemp,1,charindex(',',@strTemp)-1)
set @strTemp=substring(@strTemp,charindex(',',@strTemp)+1,8000)
end

if(charindex(' desc',@strSingle)=0)
set @strSingle=replace(@strSingle,' asc','')+' desc'
else
set @strSingle=replace(@strSingle,' desc','')
print(@strSingle)
set @OrderUpend=@OrderUpend+@strSpace+@strSingle
set @strSpace=','

end
end
--反转排序规则结束--
print('排序规则：'+@OrderString)
print('反转后：'+@OrderUpend)

--统计记录数开始--
create table #tempTBcount(myCount int)
exec('insert into #tempTBcount select count(0) from ( '+@QueryString+' ) '+@ViewName+'')
select @RecordCount=mycount from #tempTBcount drop table #tempTBcount
--统计记录数结束--

declare @PageCount int --总页数
DECLARE @PassRecordCount int --预先取出的记录数
if(@RecordCount%@PageSize=0)
set @PageCount=@RecordCount/@PageSize
else
set @PageCount=(@RecordCount-@RecordCount%@PageSize)/@PageSize+1

if (@PageCurr=1)
begin
set @RecordQuery='select top '+CONVERT(nvarchar, @PageSize)+' '+@FieldShow+' from ('+
@QueryString+') '+@ViewName+'_1 order by '+@OrderString
print('只是显示第一页数据')
print('实际执行的查询为：'+@RecordQuery)
exec(@RecordQuery)
return
end

if(@PageCurr>@PageCount)
begin
set @RecordQuery='select top 0 '+@FieldShow+' from('+
@QueryString+') '+@ViewName
print('总记录显示不了这么多页')
print('实际执行的查询为：'+@RecordQuery)
exec(@RecordQuery)
return
end

if(@PageCurr * 2<=@PageCount)
begin
set @PassRecordCount=@PageCurr*@PageSize
set @RecordQuery=' select top '+CONVERT(nvarchar, @PageSize)+' '+@FieldShow+' from ( '+
'select top '+ CONVERT(nvarchar, @PageSize) +' * from ( '+
'select top '+ CONVERT(nvarchar, @PassRecordCount) +' * from ( '+
@QueryString+' ) '+@ViewName+'_1 order by '+@OrderString+
') '+@ViewName+'_2 order by '+@OrderUpend+
') '+@ViewName+'_3 order by '+@OrderString

print('头部截取')
print('实际执行的查询为：'+@RecordQuery)
exec(@RecordQuery)
end

else
begin
set @PassRecordCount=@RecordCount-(@PageCurr-1)*@PageSize
set @RecordQuery=' select top '+CONVERT(nvarchar, @PageSize)+' '+@FieldShow+' from ( '+
'select top '+ CONVERT(nvarchar, @PassRecordCount) +' * from ( '+
@QueryString+' ) '+@ViewName+'_1 order by '+@OrderUpend+
') '+@ViewName+'_2 order by '+@OrderString
print('尾部截取')
print('实际执行的查询为：'+@RecordQuery)
exec(@RecordQuery)
return
end

select * from city

insert into [city](cityid,[city],father) select (cityid,[city],father) from [city]

select count(1) from area

create proc aa
as
begin
declare @int int
set @int=0
while @int<10
begin
insert into area(areaid,area,father) select areaid,area,father from area
set @int=@int+1
end
end
go

exec p_show 'select * from area',100,100,'*',''

create index greadiddesc on companyinfo(GenderID desc)

select * from companyinfo where sort='397' and companyname like '%浙江黄岩%' order by addtime desc

EXEC pages 'companyinfo','c_id','*',100,1091,' ','','c_id desc, addtime desc'

select top 10 * from companyinfo where ltrim(dealtype)='生产型'

select count(1) from companyinfo where sort='397'

本文链接：https://blog.csdn.net/wangdetian161/article/details/83227369

原作者删帖不实内容删帖广告或垃圾文章投诉

智能推荐

什么是内部类？成员内部类、静态内部类、局部内部类和匿名内部类的区别及作用？_成员内部类和局部内部类的区别-程序员宅基地

文章浏览阅读3.4k次，点赞8次，收藏42次。一、什么是内部类？or 内部类的概念内部类是定义在另一个类中的类；下面类TestB是类TestA的内部类。即内部类对象引用了实例化该内部对象的外围类对象。public class TestA{ class TestB {}}二、为什么需要内部类？or 内部类有什么作用？1、内部类方法可以访问该类定义所在的作用域中的数据，包括私有数据。2、内部类可以对同一个包中的其他类隐藏起来。3、当想要定义一个回调函数且不想编写大量代码时，使用匿名内部类比较便捷。三、内部类的分类成员内部_成员内部类和局部内部类的区别

分布式系统_分布式系统运维工具-程序员宅基地

文章浏览阅读118次。分布式系统要求拆分分布式思想的实质搭配要求分布式系统要求按照某些特定的规则将项目进行拆分。如果将一个项目的所有模板功能都写到一起，当某个模块出现问题时将直接导致整个服务器出现问题。拆分按照业务拆分为不同的服务器，有效的降低系统架构的耦合性在业务拆分的基础上可按照代码层级进行拆分(view、controller、service、pojo)分布式思想的实质分布式思想的实质是为了系统的..._分布式系统运维工具

用Exce分析l数据极简入门_exce l趋势分析数据量-程序员宅基地

文章浏览阅读174次。1.数据源准备2.数据处理step1：数据表处理应用函数：①VLOOKUP函数; ② CONCATENATE函数终表：step2：数据透视表统计分析（1）透视表汇总不同渠道用户数, 金额（2）透视表汇总不同日期购买用户数，金额（3）透视表汇总不同用户购买订单数，金额step3：讲第二步结果可视化, 比如, 柱形图（1）不同渠道用户数, 金额（2）不同日期..._exce l趋势分析数据量

宁盾堡垒机双因素认证方案_horizon宁盾双因素配置-程序员宅基地

文章浏览阅读3.3k次。堡垒机可以为企业实现服务器、网络设备、数据库、安全设备等的集中管控和安全可靠运行，帮助IT运维人员提高工作效率。通俗来说，就是用来控制哪些人可以登录哪些资产（事先防范和事中控制），以及录像记录登录资产后做了什么事情（事后溯源）。由于堡垒机内部保存着企业所有的设备资产和权限关系，是企业内部信息安全的重要一环。但目前出现的以下问题产生了很大安全隐患：密码设置过于简单，容易被暴力破解；为方便记忆，设置统一的密码，一旦单点被破，极易引发全面危机。在单一的静态密码验证机制下，登录密码是堡垒机安全的唯一_horizon宁盾双因素配置

谷歌浏览器安装（Win、Linux、离线安装）_chrome linux debian离线安装依赖-程序员宅基地

文章浏览阅读7.7k次，点赞4次，收藏16次。Chrome作为一款挺不错的浏览器，其有着诸多的优良特性，并且支持跨平台。其支持（Windows、Linux、Mac OS X、BSD、Android），在绝大多数情况下，其的安装都很简单，但有时会由于网络原因，无法安装，所以在这里总结下Chrome的安装。Windows下的安装：在线安装：离线安装：Linux下的安装：在线安装：离线安装：..._chrome linux debian离线安装依赖

烤仔TVの尚书房 | 逃离北上广？不如押宝越南“北上广”-程序员宅基地

文章浏览阅读153次。中国发达城市榜单每天都在刷新，但无非是北上广轮流坐庄。北京拥有最顶尖的文化资源，上海是“摩登”的国际化大都市，广州是活力四射的千年商都。GDP和发展潜力是衡量城市的数字指...

随便推点

java spark的使用和配置_使用java调用spark注册进去的程序-程序员宅基地

文章浏览阅读3.3k次。前言spark在java使用比较少，多是scala的用法，我这里介绍一下我在项目中使用的代码配置详细算法的使用请点击我主页列表查看版本jar版本说明spark3.0.1scala2.12这个版本注意和spark版本对应，只是为了引jar包springboot版本2.3.2.RELEASEmaven <dependency> <gro_使用java调用spark注册进去的程序

汽车零部件开发工具巨头V公司全套bootloader中UDS协议栈源代码，自己完成底层外设驱动开发后，集成即可使用_uds协议栈源代码-程序员宅基地

文章浏览阅读4.8k次。汽车零部件开发工具巨头V公司全套bootloader中UDS协议栈源代码，自己完成底层外设驱动开发后，集成即可使用，代码精简高效，大厂出品有量产保证。:139800617636213023darcy169_uds协议栈源代码

AUTOSAR基础篇之OS(下)_autosar 定义了 5 种多核支持类型-程序员宅基地

文章浏览阅读4.6k次，点赞20次，收藏148次。AUTOSAR基础篇之OS(下)前言首先，请问大家几个小小的问题，你清楚：你知道多核OS在什么场景下使用吗？多核系统OS又是如何协同启动或者关闭的呢？AUTOSAR OS存在哪些功能安全等方面的要求呢？多核OS之间的启动关闭与单核相比又存在哪些异同呢？。。。。。。今天，我们来一起探索并回答这些问题。为了便于大家理解，以下是本文的主题大纲：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JCXrdI0k-1636287756923)(https://gite_autosar 定义了 5 种多核支持类型

VS报错无法打开自己写的头文件_vs2013打不开自己定义的头文件-程序员宅基地

文章浏览阅读2.2k次，点赞6次，收藏14次。原因：自己写的头文件没有被加入到方案的包含目录中去，无法被检索到，也就无法打开。将自己写的头文件都放入header files。然后在VS界面上，右键方案名，点击属性。将自己头文件夹的目录添加进去。_vs2013打不开自己定义的头文件

【Redis】Redis基础命令集详解_redis命令-程序员宅基地

文章浏览阅读3.3w次，点赞80次，收藏342次。此时，可以将系统中所有用户的 Session 数据全部保存到 Redis 中，用户在提交新的请求后，系统先从Redis 中查找相应的Session 数据，如果存在，则再进行相关操作，否则跳转到登录页面。此时，可以将系统中所有用户的 Session 数据全部保存到 Redis 中，用户在提交新的请求后，系统先从Redis 中查找相应的Session 数据，如果存在，则再进行相关操作，否则跳转到登录页面。当数据量很大时，count 的数量的指定可能会不起作用，Redis 会自动调整每次的遍历数目。_redis命令

URP渲染管线简介-程序员宅基地

文章浏览阅读449次，点赞3次，收藏3次。URP的设计目标是在保持高性能的同时，提供更多的渲染功能和自定义选项。与普通项目相比，会多出Presets文件夹，里面包含着一些设置，包括本色，声音，法线，贴图等设置。全局只有主光源和附加光源，主光源只支持平行光，附加光源数量有限制，主光源和附加光源在一次Pass中可以一起着色。URP：全局只有主光源和附加光源，主光源只支持平行光，附加光源数量有限制，一次Pass可以计算多个光源。可编程渲染管线：渲染策略是可以供程序员定制的，可以定制的有：光照计算和光源，深度测试，摄像机光照烘焙，后期处理策略等等。_urp渲染管线