如何简化基于多行数据的SQL行内计算操作？

场景说明 #

现有如下结构的表格：

需要计算公式：var_5 = (var_1 * var_4) - var_3 / var_2，原方法需逐个声明变量获取数据，操作繁琐，以下是几种简化方案：

方案1：单条UPDATE结合条件聚合 #

无需逐个声明变量，直接在UPDATE语句内通过条件聚合提取所需变量并计算：

UPDATE t
SET Value = (
    SELECT (MAX(CASE WHEN Name = 'var_1' THEN Value END) * MAX(CASE WHEN Name = 'var_4' THEN Value END)) 
           - MAX(CASE WHEN Name = 'var_3' THEN Value END) / MAX(CASE WHEN Name = 'var_2' THEN Value END)
    FROM YourTable
)
FROM YourTable t
WHERE t.Name = 'var_5';

或者用PIVOT将行转列后计算，可读性更强：

WITH PivotedVars AS (
    SELECT var_1, var_2, var_3, var_4
    FROM YourTable
    PIVOT (
        MAX(Value) FOR Name IN (var_1, var_2, var_3, var_4)
    ) AS PivotTable
)
UPDATE t
SET Value = (pv.var_1 * pv.var_4) - pv.var_3 / pv.var_2
FROM YourTable t
CROSS JOIN PivotedVars pv
WHERE t.Name = 'var_5';

方案2：复用变量映射表（多计算场景） #

如果需要计算多个结果（如var_6、var_7），可先将所有变量存入表变量，后续直接复用：

DECLARE @Vars TABLE (var_1 INT, var_2 INT, var_3 INT, var_4 INT);
INSERT INTO @Vars
SELECT 
    MAX(CASE WHEN Name = 'var_1' THEN Value END),
    MAX(CASE WHEN Name = 'var_2' THEN Value END),
    MAX(CASE WHEN Name = 'var_3' THEN Value END),
    MAX(CASE WHEN Name = 'var_4' THEN Value END)
FROM YourTable;

-- 计算var_5
UPDATE YourTable
SET Value = (SELECT var_1 * var_4 - var_3 / var_2 FROM @Vars)
WHERE Name = 'var_5';

-- 复用变量计算var_6
UPDATE YourTable
SET Value = (SELECT var_1 + var_2 * var_3 FROM @Vars)
WHERE Name = 'var_6';

方案3：动态SQL（大量变量场景） #

当变量数量极多，手动编写映射逻辑效率低时，用动态SQL自动生成变量列表和计算逻辑：

DECLARE @VarList NVARCHAR(MAX), @Formula NVARCHAR(MAX);
-- 自动获取除结果变量外的所有变量名
SELECT @VarList = STRING_AGG(QUOTENAME(Name), ',')
FROM YourTable
WHERE Name NOT IN ('var_5');

-- 构建动态执行的SQL语句
DECLARE @PivotSQL NVARCHAR(MAX) = N'
WITH PivotedVars AS (
    SELECT ' + @VarList + N'
    FROM YourTable
    PIVOT (
        MAX(Value) FOR Name IN (' + @VarList + N')
    ) AS PivotTable
)
UPDATE YourTable
SET Value = (var_1 * var_4) - var_3 / var_2
WHERE Name = ''var_5'';';

EXEC sp_executesql @PivotSQL;

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