存储过程多表分页

最新推荐文章于 2024-05-06 16:41:35 发布

Allure Love

最新推荐文章于 2024-05-06 16:41:35 发布

阅读量692

点赞数

分类专栏：存储过程文章标签：分页存储

本文链接：https://blog.csdn.net/qq_35406995/article/details/78115508

版权

存储过程专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一.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 运算符。”，转换一下即可

Allure Love

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
存储过程多表分页

一.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
复制链接

扫一扫