如何在SQL Server中循环生成依赖前置列的新增计算列？

针对需要从test表的a、b列出发，批量生成N个依赖前序列的新列需求，以下提供两种可行实现方式：

方法1：动态SQL拼接（通用递推场景） #

当新列的递推逻辑无法简化时，可通过动态SQL循环拼接查询语句，自动生成指定数量的列：

DECLARE @num_cols INT = 100; -- 自定义要生成的新列数量
DECLARE @sql NVARCHAR(MAX) = N'SELECT id, a, b';
DECLARE @i INT = 1;
DECLARE @prev_expr NVARCHAR(MAX) = N'(a + b)';
DECLARE @col_name NVARCHAR(50);

WHILE @i <= @num_cols
BEGIN
    SET @col_name = N'col' + CAST(@i AS NVARCHAR(10));
    -- 拼接当前列的表达式与别名
    SET @sql = @sql + N', ' + @prev_expr + N' AS ' + QUOTENAME(@col_name);
    -- 更新递推表达式，用于下一列
    SET @prev_expr = N'(a + b + ' + QUOTENAME(@col_name) + N')';
    SET @i = @i + 1;
END

SET @sql = @sql + N' FROM test;';
EXEC sp_executesql @sql;

说明 #

• 初始从基础列id、a、b开始构建查询语句
• 循环中每一次迭代生成一个新列，表达式遵循当前列 = a + b + 上一列的递推规则
• 使用QUOTENAME函数避免列名引发的语法冲突
• 最终通过sp_executesql执行动态生成的完整查询

方法2：递推公式简化（性能优化版） #

观察需求示例，可提炼出明显的数学规律：

• col1 = a + b
• col2 = a + b + col1 = 2*(a + b)
• col3 = a + b + col2 = 3*(a + b)
• ...
• colN = N*(a + b)

基于此规律，可直接用倍数公式生成列，无需依赖前序列，性能更优：

DECLARE @num_cols INT = 100;
DECLARE @sql NVARCHAR(MAX) = N'SELECT id, a, b';
DECLARE @i INT = 1;

WHILE @i <= @num_cols
BEGIN
    SET @sql = @sql + N', ' + CAST(@i AS NVARCHAR(10)) + N'*(a + b) AS ' + QUOTENAME(N'col' + CAST(@i AS NVARCHAR(10)));
    SET @i = @i + 1;
END

SET @sql = @sql + N' FROM test;';
EXEC sp_executesql @sql;

注意事项 #

• 若生成超过26列，建议用col1、col2这类命名替代字母命名，避免字母不足的问题
• 大量列会导致结果集过宽，可能降低查询性能，建议评估是否需要将数据转为行存储（比如把每个计算结果作为一行记录）
• 由于a、b是NUMERIC(3,2)类型，多次累加可能出现精度溢出，可将结果列类型调整为NUMERIC(10,2)或更高精度

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