sql server新增个能够自动补行和增加分组号的存储过程-羽化飞翔

sql server新增个能够自动补行和增加分组号的存储过程的使用场景

在 SQL Server 中，有时我们需要把一张表按照固定行数进行分组。例如每 5 行为一组，前 5 行分组号为 1，接下来 5 行分组号为 2，以此类推。如果最后一组数据不足指定行数，还需要自动补充空行，使每一组都保持固定行数。

例如原始数据只有 7 行，每组 5 行，最终结果应该返回 10 行：

第 1 - 5 行：分组号 = 1
第 6 - 7 行：分组号 = 2
第 8 - 10 行：补空行，分组号 = 2

这个需求看起来简单，但在 SQL Server 2008 R2 中会遇到几个限制：

不支持 THROW
不支持 STRING_AGG
不支持 CREATE OR ALTER
函数不能接收任意表结构
函数内部不能执行动态 SQL
临时表需要从 tempdb 读取元数据
补行逻辑如果写得不好，即使只有 1 行数据也可能非常慢

因此，这类需求更适合使用存储过程实现。存储过程可以通过动态 SQL 获取原表字段，再额外拼接分组号和补空行。

sql server新增个能够自动补行和增加分组号的存储过程的解决方案

一、使用存储过程，而不是函数

SQL Server 函数不能动态接收任意表，也不能执行动态 SQL。所以如果要支持普通表、临时表，并且动态返回原表所有字段，应该使用存储过程。

建议调用方式如下：

EXEC dbo.增加分组列
    @TableName = '#Student',
    @RowsPerGroup = 5,
    @OrderBy = 'Id';

参数含义如下：

@TableName：表名，支持普通表和 #临时表
@RowsPerGroup：每组多少行
@OrderBy：排序列

这里必须传入排序列，因为 SQL Server 表本身没有天然顺序。如果不指定排序字段，“从上到下”这个概念是不稳定的。

二、SQL Server 2008 R2 兼容处理

SQL Server 2008 R2 不能使用下面这些新语法：

THROW
STRING_AGG
CREATE OR ALTER

需要替换为下面这些兼容写法：

RAISERROR
FOR XML PATH
ALTER PROCEDURE

例如参数校验可以这样写：

IF @RowsPerGroup IS NULL OR @RowsPerGroup <= 0
BEGIN
    RAISERROR('RowsPerGroup 必须大于 0', 16, 1);
    RETURN;
END;

动态拼接列名时，不能使用 STRING_AGG，需要使用 FOR XML PATH：

SELECT @ColumnList = STUFF
(
    (
        SELECT ', s.' + QUOTENAME(name)
        FROM sys.columns
        WHERE object_id = @ObjectId
        ORDER BY column_id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'),
    1,
    2,
    ''
);

三、支持普通表和临时表

如果传入的是普通表，例如 dbo.Student，可以直接通过当前数据库的 sys.columns 获取字段信息。

如果传入的是临时表，例如 #Student，就不能直接使用 OBJECT_ID(‘#Student’)，而应该从 tempdb 读取：

OBJECT_ID('tempdb..#Student')

临时表的字段信息也应该从 tempdb.sys.columns 获取：

SELECT name
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('tempdb..#Student');

临时表调用时，需要保证临时表和存储过程在同一个连接中执行：

CREATE TABLE #Student
(
    Id INT,
    Name NVARCHAR(50),
    Score INT
);

INSERT INTO #Student VALUES
(1, N'张三', 90);

EXEC dbo.增加分组列
    @TableName = '#Student',
    @RowsPerGroup = 5,
    @OrderBy = 'Id';

上面这个例子中，原始数据只有 1 行，每组要求 5 行，所以最终会返回 5 行，其中后 4 行是补出来的空行。

四、性能问题的原因

最开始的补行方案使用了系统表笛卡尔积来生成数字行：

FROM sys.all_objects a
CROSS JOIN sys.all_objects b

这种写法虽然可以生成大量数字，但在 SQL Server 2008 R2 中可能非常慢。即使原表只有 1 行，只需要补 4 行，SQL Server 也可能因为系统表扫描、笛卡尔积和排序导致执行时间非常长。

实际上，补行数量最多只会是：

@RowsPerGroup - 1

例如每组 5 行，最多只需要补 4 行。因此不应该为了补几行去生成一个很大的数字集合。

优化后的做法是使用递归 CTE，只生成真正需要补的空行：

pad_nums AS
(
    SELECT 1 AS n
    WHERE @PadCount > 0

    UNION ALL

    SELECT n + 1
    FROM pad_nums
    WHERE n < @PadCount
)

这样如果只需要补 4 行，就只生成 4 行，性能会明显提升。

五、优化后的完整存储过程

下面是兼容 SQL Server 2008 R2 的完整优化版，支持普通表、临时表、动态字段、自动补空行和增加分组号：

USE [JTH]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[增加分组列]
    @TableName NVARCHAR(256),  -- 普通表：'dbo.Student'；临时表：'#Student'
    @RowsPerGroup INT,         -- 每组行数，例如 5
    @OrderBy SYSNAME           -- 排序列名，例如 'Id'
AS
BEGIN
    SET NOCOUNT ON;

    IF @RowsPerGroup IS NULL OR @RowsPerGroup <= 0
    BEGIN
        RAISERROR('RowsPerGroup 必须大于 0', 16, 1);
        RETURN;
    END;

    DECLARE @IsTempTable BIT;
    DECLARE @SchemaName SYSNAME;
    DECLARE @PureTableName SYSNAME;
    DECLARE @SafeTableName NVARCHAR(300);
    DECLARE @ObjectId INT;

    DECLARE @SourceColumnList NVARCHAR(MAX);
    DECLARE @PlainColumnList NVARCHAR(MAX);
    DECLARE @OutputColumnList NVARCHAR(MAX);
    DECLARE @NullColumnList NVARCHAR(MAX);

    DECLARE @Sql NVARCHAR(MAX);

    SET @IsTempTable =
        CASE
            WHEN LEFT(@TableName, 1) = '#' THEN 1
            ELSE 0
        END;

    IF @IsTempTable = 1
    BEGIN
        SET @PureTableName = @TableName;
        SET @SafeTableName = QUOTENAME(@PureTableName);
        SET @ObjectId = OBJECT_ID('tempdb..' + @PureTableName);

        IF @ObjectId IS NULL
        BEGIN
            RAISERROR('临时表不存在，请确认临时表已在当前连接中创建', 16, 1);
            RETURN;
        END;

        IF NOT EXISTS
        (
            SELECT 1
            FROM tempdb.sys.columns
            WHERE object_id = @ObjectId
              AND name = @OrderBy
        )
        BEGIN
            RAISERROR('排序列不存在', 16, 1);
            RETURN;
        END;

        SELECT @SourceColumnList = STUFF
        (
            (
                SELECT ', s.' + QUOTENAME(name)
                FROM tempdb.sys.columns
                WHERE object_id = @ObjectId
                ORDER BY column_id
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1,
            2,
            ''
        );

        SELECT @PlainColumnList = STUFF
        (
            (
                SELECT ', ' + QUOTENAME(name)
                FROM tempdb.sys.columns
                WHERE object_id = @ObjectId
                ORDER BY column_id
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1,
            2,
            ''
        );

        SELECT @OutputColumnList = STUFF
        (
            (
                SELECT ', q.' + QUOTENAME(name)
                FROM tempdb.sys.columns
                WHERE object_id = @ObjectId
                ORDER BY column_id
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1,
            2,
            ''
        );

        SELECT @NullColumnList = STUFF
        (
            (
                SELECT ', NULL AS ' + QUOTENAME(name)
                FROM tempdb.sys.columns
                WHERE object_id = @ObjectId
                ORDER BY column_id
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1,
            2,
            ''
        );
    END
    ELSE
    BEGIN
        SET @SchemaName = ISNULL(PARSENAME(@TableName, 2), 'dbo');
        SET @PureTableName = PARSENAME(@TableName, 1);

        IF @PureTableName IS NULL
        BEGIN
            RAISERROR('表名格式不正确，应传入 dbo.TableName、TableName 或 #TempTable', 16, 1);
            RETURN;
        END;

        SET @SafeTableName = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@PureTableName);
        SET @ObjectId = OBJECT_ID(@SafeTableName);

        IF @ObjectId IS NULL
        BEGIN
            RAISERROR('表不存在', 16, 1);
            RETURN;
        END;

        IF NOT EXISTS
        (
            SELECT 1
            FROM sys.columns
            WHERE object_id = @ObjectId
              AND name = @OrderBy
        )
        BEGIN
            RAISERROR('排序列不存在', 16, 1);
            RETURN;
        END;

        SELECT @SourceColumnList = STUFF
        (
            (
                SELECT ', s.' + QUOTENAME(name)
                FROM sys.columns
                WHERE object_id = @ObjectId
                ORDER BY column_id
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1,
            2,
            ''
        );

        SELECT @PlainColumnList = STUFF
        (
            (
                SELECT ', ' + QUOTENAME(name)
                FROM sys.columns
                WHERE object_id = @ObjectId
                ORDER BY column_id
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1,
            2,
            ''
        );

        SELECT @OutputColumnList = STUFF
        (
            (
                SELECT ', q.' + QUOTENAME(name)
                FROM sys.columns
                WHERE object_id = @ObjectId
                ORDER BY column_id
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1,
            2,
            ''
        );

        SELECT @NullColumnList = STUFF
        (
            (
                SELECT ', NULL AS ' + QUOTENAME(name)
                FROM sys.columns
                WHERE object_id = @ObjectId
                ORDER BY column_id
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1,
            2,
            ''
        );
    END;

    SET @Sql = N'
DECLARE @TotalCount INT;
DECLARE @PadCount INT;

SELECT @TotalCount = COUNT(1)
FROM ' + @SafeTableName + N' AS s;

SET @PadCount =
    CASE
        WHEN @TotalCount = 0 THEN 0
        WHEN @TotalCount % @RowsPerGroup = 0 THEN 0
        ELSE @RowsPerGroup - (@TotalCount % @RowsPerGroup)
    END;

WITH src AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY s.' + QUOTENAME(@OrderBy) + N') AS rn,
        ' + @SourceColumnList + N'
    FROM ' + @SafeTableName + N' AS s
),
pad_nums AS
(
    SELECT 1 AS n
    WHERE @PadCount > 0

    UNION ALL

    SELECT n + 1
    FROM pad_nums
    WHERE n < @PadCount
)
SELECT
    ' + @OutputColumnList + N',
    q.[_增加分组列_分组号] AS [分组号]
FROM
(
    SELECT
        rn AS [_增加分组列_排序号],
        ' + @PlainColumnList + N',
        ((rn - 1) / @RowsPerGroup) + 1 AS [_增加分组列_分组号]
    FROM src

    UNION ALL

    SELECT
        @TotalCount + n AS [_增加分组列_排序号],
        ' + @NullColumnList + N',
        ((@TotalCount + n - 1) / @RowsPerGroup) + 1 AS [_增加分组列_分组号]
    FROM pad_nums
) q
ORDER BY q.[_增加分组列_排序号]
OPTION (MAXRECURSION 0);
';

    EXEC sp_executesql
        @Sql,
        N'@RowsPerGroup INT',
        @RowsPerGroup = @RowsPerGroup;
END;
GO

六、临时表调用示例

如果数据来自临时表，可以这样调用：

CREATE TABLE #Student
(
    Id INT,
    Name NVARCHAR(50),
    Score INT
);

INSERT INTO #Student VALUES
(1, N'张三', 90),
(2, N'李四', 88),
(3, N'王五', 76),
(4, N'赵六', 95),
(5, N'钱七', 82),
(6, N'孙八', 70),
(7, N'周九', 66);

EXEC dbo.增加分组列
    @TableName = '#Student',
    @RowsPerGroup = 5,
    @OrderBy = 'Id';

返回结果逻辑如下：

第 1 - 5 行：原始数据，分组号 = 1
第 6 - 7 行：原始数据，分组号 = 2
第 8 - 10 行：补空行，分组号 = 2

七、索引优化建议

如果数据量较大，真正影响性能的通常是排序列。因为存储过程内部使用了 ROW_NUMBER()，它依赖 ORDER BY 指定的排序字段。

如果排序列没有索引，SQL Server 需要对数据进行排序，数据量大时会比较慢。

普通表可以给排序列创建索引：

CREATE INDEX IX_Student_Id ON dbo.Student(Id);

临时表可以在插入数据后创建聚集索引：

CREATE CLUSTERED INDEX IX_Student_Id ON #Student(Id);

如果你的数据主要来自临时表，给临时表的排序列建聚集索引，通常比继续改存储过程更有效。

八、如果仍然很慢，需要检查锁等待

如果优化补行逻辑后，只有 1 行数据仍然需要十几秒甚至几十秒，那么问题可能不是 SQL 逻辑本身，而是当前查询被其他会话阻塞。

可以在执行很慢时，另开一个查询窗口执行：

EXEC sp_who2;

如果当前会话的 BlkBy 有值，说明被其他会话阻塞了，需要先处理锁等待问题。

sql server新增个能够自动补行和增加分组号的存储过程的结论

在 SQL Server 2008 R2 中，实现“任意表增加分组列，并按固定行数自动补空行”的需求，推荐使用存储过程，而不是函数。

这个方案的关键点有三个：

用 RAISERROR 和 FOR XML PATH 兼容 SQL Server 2008 R2
普通表读取 sys.columns，临时表读取 tempdb.sys.columns
不要使用 sys.all_objects CROSS JOIN sys.all_objects 生成补行
只生成实际缺少的空行，再用 UNION ALL 拼接到原始数据后面

如果只有 1 行数据、每组 5 行，优化后只需要补 4 行，正常情况下应该是毫秒级返回，而不应该出现 20 多秒的耗时。

整体思路可以概括为：

原始数据直接输出
只对最后一组缺失的部分补空行
不要为了补几行生成大量无关数字再关联

这个改法既能保持通用性，又能兼容 SQL Server 2008 R2，同时解决临时表支持和补行性能问题。

sql server新增个能够自动补行和增加分组号的存储过程的完整代码

此处内容已隐藏，请评论后刷新页面查看.

重要提示

虚拟资源具有可复制性： 建议具有一定思考和动手能力的用户获取，并且学习使用。本站资源均为网友收集整理而来，仅供学习和研究使用。
请谨慎考虑： 小白用户和缺乏思考动手能力者不建议赞助。
虚拟商品购买须知： 虚拟类商品，一经打赏赞助，不支持退款。请谅解，谢谢合作！
如有解压密码： 看下载页、看下载页、看下载页。

文章版权声明 1、本网站名称：羽化飞翔
2、本站永久网址：https://www.huazai186.com
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，部分软件下载在学习和参考后，请24小时内进行删除，如有侵权，请发送邮件到【yearn186@qq.com】进行反馈，我们将在第一时间进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END