SQL计算列差异：手动创建差值列还是用更简便方法？

不需要手动为每种value_type创建差值列，有两种高效实现方式：

1. 静态透视（适用于value_type固定的场景） #

如果你的value_type是固定不变的，可以直接用条件聚合+透视的方式一次性计算所有差值列：

SELECT 
    sku,
    SUM(CASE WHEN value_type = 'units' AND data_type = 'actual' THEN value ELSE 0 END) 
    - SUM(CASE WHEN value_type = 'units' AND data_type = 'forecast' THEN value ELSE 0 END) AS units_diff,
    SUM(CASE WHEN value_type = 'gross sales' AND data_type = 'actual' THEN value ELSE 0 END) 
    - SUM(CASE WHEN value_type = 'gross sales' AND data_type = 'forecast' THEN value ELSE 0 END) AS gross_sales_diff,
    SUM(CASE WHEN value_type = 'net sales' AND data_type = 'actual' THEN value ELSE 0 END) 
    - SUM(CASE WHEN value_type = 'net sales' AND data_type = 'forecast' THEN value ELSE 0 END) AS net_sales_diff,
    SUM(CASE WHEN value_type = 'gross profit' AND data_type = 'actual' THEN value ELSE 0 END) 
    - SUM(CASE WHEN value_type = 'gross profit' AND data_type = 'forecast' THEN value ELSE 0 END) AS gross_profit_diff
FROM #sample
GROUP BY sku;

这个查询会直接输出每个sku对应的各类差值列，无需手动逐个创建。

2. 动态SQL（适用于value_type可能新增/变化的场景） #

如果value_type是动态变化的，可以用动态SQL自动生成所有差值列，无需修改代码：

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

-- 生成所有差值列的SQL片段
SELECT @cols = STRING_AGG(
    CONCAT(
        'SUM(CASE WHEN value_type = ''', value_type, ''' AND data_type = ''actual'' THEN value ELSE 0 END) 
        - SUM(CASE WHEN value_type = ''', value_type, ''' AND data_type = ''forecast'' THEN value ELSE 0 END) AS ', 
        REPLACE(value_type, ' ', '_'), '_diff'
    ),
    ', '
)
FROM (SELECT DISTINCT value_type FROM #sample) AS vt;

-- 拼接完整查询语句
SET @query = CONCAT(
    'SELECT sku, ', @cols, ' FROM #sample GROUP BY sku;'
);

-- 执行动态查询
EXEC sp_executesql @query;

这段代码会自动读取所有唯一的value_type，生成对应的差值列，后续新增value_type时无需修改查询逻辑。

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