如何在单次SELECT语句中为多个变量赋值？现有查询仅最后变量获值

你的查询会逐行执行变量赋值：

• 当R=1时，只有@a被赋值为165，@b和@c会被设为NULL
• 当R=2时，只有@b被赋值为487，@a和@c被设为NULL
• 当R=3时，只有@c被赋值为1807，@a和@b被设为NULL

最后一行执行完成后，@a和@b的值被覆盖为NULL，只剩@c保留了正确值。

方法1：使用聚合函数保留非NULL值 #

通过MAX()函数忽略NULL值，确保每个变量能获取到对应行的有效值，不会被后续行覆盖：

DECLARE @a INT, @b INT, @c INT;

WITH P AS (
SELECT
    ROW_NUMBER() OVER(ORDER BY A.Price) AS R,
    A.Price
FROM 
    TableA A
INNER JOIN 
    TableB B ON A.ID = B.ID
INNER JOIN 
    TableC C ON C.Code = A.Code
WHERE
    C.Type = 125
    AND A.[Desc] = 10000038 -- Desc是SQL关键字，需用方括号包裹
    AND C.YearID = 10000021
GROUP BY 
    A.Price
)
SELECT
    @a = MAX(CASE WHEN [R] = 1 THEN P.[Price] END),
    @b = MAX(CASE WHEN [R] = 2 THEN P.[Price] END),
    @c = MAX(CASE WHEN [R] = 3 THEN P.[Price] END)
FROM P;

-- 查看赋值结果
SELECT @a AS a, @b AS b, @c AS c;

方法2：使用PIVOT行转列后赋值 #

先通过PIVOT将多行数据转为单行多列，再直接给变量赋值：

DECLARE @a INT, @b INT, @c INT;

WITH P AS (
SELECT
    ROW_NUMBER() OVER(ORDER BY A.Price) AS R,
    A.Price
FROM 
    TableA A
INNER JOIN 
    TableB B ON A.ID = B.ID
INNER JOIN 
    TableC C ON C.Code = A.Code
WHERE
    C.Type = 125
    AND A.[Desc] = 10000038
    AND C.YearID = 10000021
GROUP BY 
    A.Price
),
PivotedData AS (
    SELECT [1] AS Price1, [2] AS Price2, [3] AS Price3
    FROM P
    PIVOT (
        MAX(Price)
        FOR R IN ([1], [2], [3])
    ) AS PivotResult
)
SELECT @a = Price1, @b = Price2, @c = Price3
FROM PivotedData;

-- 查看赋值结果
SELECT @a AS a, @b AS b, @c AS c;

注意事项 #

Desc是SQL的保留关键字，直接作为列名使用可能引发语法错误，建议始终用方括号[Desc]包裹该列名。

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