SQL存储过程：将指定Bin的Top N行映射到不同输出参数的问题

我SQL知识有限，现有一张记录Bin零件计数变更的表Automation_Stock，需要编写存储过程筛选指定Bin（通过@GBT_COLUMN_A指定）的前3条最新记录，把每条记录的列值分别存入对应的参数组（首行存参数组1，次行存参数组2，第三行存参数组3）。

当前编写的存储过程只会把首行数据填充到所有参数组中，无法获取后续行的数据。另外，这些数据需要通过Transaction Manager发送给Allen-Bradley PLC，求解决办法。

原存储过程代码 #

ALTER PROCEDURE [dbo].[GBT_SPROC]
    
    @GBT_COLUMN_A VARCHAR(255),
    @GBT_COLUMN_B_1 INT OUT,
    @GBT_COLUMN_C_1 VARCHAR(255) OUT,
    @GBT_COLUMN_D_1 VARCHAR(255) OUT,
    @GBT_COLUMN_E_1 VARCHAR(255) OUT,
    @GBT_COLUMN_B_2 INT OUT,
    @GBT_COLUMN_C_2 VARCHAR(255) OUT,
    @GBT_COLUMN_D_2 VARCHAR(255) OUT,
    @GBT_COLUMN_E_2 VARCHAR(255) OUT,
    @GBT_COLUMN_B_3 INT OUT,
    @GBT_COLUMN_C_3 VARCHAR(255) OUT,
    @GBT_COLUMN_D_3 VARCHAR(255) OUT,
    @GBT_COLUMN_E_3 VARCHAR(255) OUT
    
    
AS
BEGIn
    
    SET NOCOUNT ON;

    select TOP (3)
    @GBT_COLUMN_B_1 = COLUMN_B,
    @GBT_COLUMN_C_1 = COLUMN_C,
    @GBT_COLUMN_D_1 = COLUMN_D,
    @GBT_COLUMN_E_1 = COLUMN_E,
    @GBT_COLUMN_B_2 = COLUMN_B,
    @GBT_COLUMN_C_2 = COLUMN_C,
    @GBT_COLUMN_D_2 = COLUMN_D,
    @GBT_COLUMN_E_2 = COLUMN_E,
    @GBT_COLUMN_B_3 = COLUMN_B,
    @GBT_COLUMN_C_3 = COLUMN_C,
    @GBT_COLUMN_D_3 = COLUMN_D,
    @GBT_COLUMN_E_3 = COLUMN_E
    
    FROM Automation_Stock

    WHERE @GBT_COLUMN_A = COLUMN_A
    ORDER BY Date_and_Time desc
    
    
END

问题分析 #

原存储过程的写法是在SELECT TOP 3时，把每一行的列值同时赋值给所有参数，最终变量只会保留最后一行的赋值结果（或当只有一行时所有参数都是该行数据），无法实现每行对应不同参数组的需求。

修正后的存储过程 #

ALTER PROCEDURE [dbo].[GBT_SPROC]
    @GBT_COLUMN_A VARCHAR(255),
    @GBT_COLUMN_B_1 INT OUT,
    @GBT_COLUMN_C_1 VARCHAR(255) OUT,
    @GBT_COLUMN_D_1 VARCHAR(255) OUT,
    @GBT_COLUMN_E_1 VARCHAR(255) OUT,
    @GBT_COLUMN_B_2 INT OUT,
    @GBT_COLUMN_C_2 VARCHAR(255) OUT,
    @GBT_COLUMN_D_2 VARCHAR(255) OUT,
    @GBT_COLUMN_E_2 VARCHAR(255) OUT,
    @GBT_COLUMN_B_3 INT OUT,
    @GBT_COLUMN_C_3 VARCHAR(255) OUT,
    @GBT_COLUMN_D_3 VARCHAR(255) OUT,
    @GBT_COLUMN_E_3 VARCHAR(255) OUT
AS
BEGIN
    SET NOCOUNT ON;

    -- 初始化所有输出参数为NULL，避免无对应行时保留旧值
    SELECT 
        @GBT_COLUMN_B_1 = NULL, @GBT_COLUMN_C_1 = NULL, @GBT_COLUMN_D_1 = NULL, @GBT_COLUMN_E_1 = NULL,
        @GBT_COLUMN_B_2 = NULL, @GBT_COLUMN_C_2 = NULL, @GBT_COLUMN_D_2 = NULL, @GBT_COLUMN_E_2 = NULL,
        @GBT_COLUMN_B_3 = NULL, @GBT_COLUMN_C_3 = NULL, @GBT_COLUMN_D_3 = NULL, @GBT_COLUMN_E_3 = NULL;

    -- 用ROW_NUMBER()给记录按时间倒序编号，取前3条
    WITH RankedRecords AS (
        SELECT 
            COLUMN_B, COLUMN_C, COLUMN_D, COLUMN_E,
            ROW_NUMBER() OVER (ORDER BY Date_and_Time DESC) AS RowNum
        FROM Automation_Stock
        WHERE COLUMN_A = @GBT_COLUMN_A
    )
    -- 根据行号将对应记录赋值给指定参数组
    SELECT
        @GBT_COLUMN_B_1 = CASE WHEN RowNum = 1 THEN COLUMN_B ELSE @GBT_COLUMN_B_1 END,
        @GBT_COLUMN_C_1 = CASE WHEN RowNum = 1 THEN COLUMN_C ELSE @GBT_COLUMN_C_1 END,
        @GBT_COLUMN_D_1 = CASE WHEN RowNum = 1 THEN COLUMN_D ELSE @GBT_COLUMN_D_1 END,
        @GBT_COLUMN_E_1 = CASE WHEN RowNum = 1 THEN COLUMN_E ELSE @GBT_COLUMN_E_1 END,
        @GBT_COLUMN_B_2 = CASE WHEN RowNum = 2 THEN COLUMN_B ELSE @GBT_COLUMN_B_2 END,
        @GBT_COLUMN_C_2 = CASE WHEN RowNum = 2 THEN COLUMN_C ELSE @GBT_COLUMN_C_2 END,
        @GBT_COLUMN_D_2 = CASE WHEN RowNum = 2 THEN COLUMN_D ELSE @GBT_COLUMN_D_2 END,
        @GBT_COLUMN_E_2 = CASE WHEN RowNum = 2 THEN COLUMN_E ELSE @GBT_COLUMN_E_2 END,
        @GBT_COLUMN_B_3 = CASE WHEN RowNum = 3 THEN COLUMN_B ELSE @GBT_COLUMN_B_3 END,
        @GBT_COLUMN_C_3 = CASE WHEN RowNum = 3 THEN COLUMN_C ELSE @GBT_COLUMN_C_3 END,
        @GBT_COLUMN_D_3 = CASE WHEN RowNum = 3 THEN COLUMN_D ELSE @GBT_COLUMN_D_3 END,
        @GBT_COLUMN_E_3 = CASE WHEN RowNum = 3 THEN COLUMN_E ELSE @GBT_COLUMN_E_3 END
    FROM RankedRecords
    WHERE RowNum IN (1,2,3);
END

关键说明 #

• 行号标记：使用ROW_NUMBER()函数为筛选后的记录按时间倒序生成唯一行号，明确区分每条记录的顺序
• 参数初始化：先将所有输出参数设为NULL，避免当指定Bin的记录不足3条时，参数保留之前的旧值
• 条件赋值：通过CASE语句根据行号将对应记录的列值赋值给对应的参数组，确保每行数据对应到正确的参数
• 兼容性：支持记录数不足3条的场景，不足的参数组会保持NULL，符合PLC对接的空值处理逻辑

内容的提问来源于stack exchange，提问作者TJan