在SQL中生成多变量全值组合的动态查询方案问询

嘿，针对你需要生成所有变量值组合的需求，我来给你分享几个在数据库层面的实现方案——毕竟百万级组合在应用层处理确实会慢到让人抓狂，数据库才是干这个的好手！

一、当前使用的MSSQL实现方案 #

假设你的源表名为VariableValues，结构是Variable(字符串类型)、Value(字符串类型)。这里推荐两种方案，其中动态SQL更适合变量数量不固定、数据量较大的场景。

方案1：动态SQL生成笛卡尔积（推荐） #

这个方案会自动识别所有变量，动态生成交叉连接语句，最后转成你需要的行格式，支持MSSQL 2017及以上版本：

DECLARE @DynamicSQL NVARCHAR(MAX)
DECLARE @Variables NVARCHAR(MAX)
DECLARE @UnpivotColumns NVARCHAR(MAX)

-- 自动收集所有唯一变量，按名称排序保证组合顺序稳定
SELECT 
    @Variables = STRING_AGG(QUOTENAME(Variable), ' CROSS JOIN ') WITHIN GROUP (ORDER BY Variable),
    @UnpivotColumns = STRING_AGG(QUOTENAME(Variable), ',') WITHIN GROUP (ORDER BY Variable)
FROM (SELECT DISTINCT Variable FROM VariableValues) AS Vars

-- 构建动态SQL：生成笛卡尔积 → 分配组合ID → 转成行格式
SET @DynamicSQL = N'
WITH Combinations AS (
    SELECT 
        ''comb_'' + CAST(ROW_NUMBER() OVER (ORDER BY ' + @UnpivotColumns + ') AS VARCHAR(20)) AS CombinationID,
        ' + @UnpivotColumns + '
    FROM 
        ' + @Variables + '
)
SELECT 
    CombinationID,
    Variable,
    Value
FROM 
    Combinations
UNPIVOT (
    Value FOR Variable IN (' + @UnpivotColumns + ')
) AS Unpivoted
ORDER BY 
    CombinationID, Variable'

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL

兼容MSSQL 2016及以下版本的适配

如果你的MSSQL版本不支持STRING_AGG，可以用STUFF+FOR XML PATH替换变量拼接部分：

-- 替换原有的变量拼接逻辑
SELECT 
    @Variables = STUFF((SELECT ' CROSS JOIN ' + QUOTENAME(Variable)
                       FROM (SELECT DISTINCT Variable FROM VariableValues) AS Vars
                       ORDER BY Variable
                       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 13, ''),
    @UnpivotColumns = STUFF((SELECT ',' + QUOTENAME(Variable)
                           FROM (SELECT DISTINCT Variable FROM VariableValues) AS Vars
                           ORDER BY Variable
                           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

方案2：递归CTE（适合变量数量较少的场景） #

如果你的变量数量不多（比如10个以内），递归CTE也能实现，但大数据量下效率不如动态SQL：

WITH VariableList AS (
    -- 给每个变量排序，标记索引和总变量数
    SELECT 
        Variable,
        Value,
        ROW_NUMBER() OVER (ORDER BY Variable) AS VarIndex,
        COUNT(DISTINCT Variable) OVER () AS TotalVars
    FROM VariableValues
),
RecursiveCombinations AS (
    -- 初始递归：第一个变量的所有取值作为基础组合
    SELECT 
        'comb_1' AS CombinationID,
        VarIndex,
        Variable,
        Value,
        1 AS CombNum
    FROM VariableList
    WHERE VarIndex = 1
    UNION ALL
    -- 递归连接后续变量的所有取值，生成新组合
    SELECT 
        'comb_' + CAST(rc.CombNum + (vl.ValueIndex - 1) * POWER(2, rc.VarIndex) AS VARCHAR(20)) AS CombinationID,
        vl.VarIndex,
        vl.Variable,
        vl.Value,
        rc.CombNum + (vl.ValueIndex - 1) * POWER(2, rc.VarIndex) AS CombNum
    FROM RecursiveCombinations rc
    JOIN (
        SELECT 
            Variable,
            Value,
            VarIndex,
            ROW_NUMBER() OVER (PARTITION BY Variable ORDER BY Value) AS ValueIndex
        FROM VariableList
    ) vl ON rc.VarIndex + 1 = vl.VarIndex
)
SELECT 
    CombinationID,
    Variable,
    Value
FROM RecursiveCombinations
ORDER BY CombNum, Variable

二、其他更便捷的DBMS选项 #

如果可以更换数据库，PostgreSQL在处理动态笛卡尔积和数组操作上会更灵活，语法也更简洁。比如用递归CTE生成所有组合：

WITH vars AS (
    SELECT variable, array_agg(value) AS values_arr
    FROM variable_values
    GROUP BY variable
),
recursive_combos AS (
    -- 初始递归：第一个变量的所有取值
    SELECT 
        ARRAY[(variable, value)] AS combo,
        1 AS var_idx
    FROM variable_values
    WHERE variable = (SELECT MIN(variable) FROM vars)
    UNION ALL
    -- 递归拼接后续变量的所有取值
    SELECT 
        rc.combo || ARRAY[(vv.variable, vv.value)],
        rc.var_idx + 1
    FROM recursive_combos rc
    JOIN variable_values vv ON vv.variable = (
        SELECT variable FROM vars ORDER BY variable OFFSET rc.var_idx LIMIT 1
    )
)
-- 展开组合并生成ID
SELECT 
    'comb_' || row_number() OVER () AS combinationid,
    (unnest(combo)).variable AS variable,
    (unnest(combo)).value AS value
FROM recursive_combos
WHERE var_idx = (SELECT COUNT(*) FROM vars)
ORDER BY combinationid, variable;

总结 #

• 如果你继续用MSSQL，动态SQL方案是最优选择，能高效处理百万级组合，完全适配变量数量和值数量不固定的场景。
• 若可以换DBMS，PostgreSQL的数组和递归语法能让实现更简洁，集合操作性能也很出色。

数据库层面的集合操作天生比应用层循环高效得多，百万级组合的处理速度会有质的提升。

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