SQL Server列值唯一组合实现方案（替代LEAST/GREATEST函数）

核心思路 #

SQL Server没有内置LEAST/GREATEST函数，我们可以通过强制值的顺序（让后一个值大于前一个）来避免重复组合（比如(a,b)和(b,a)视为同一组合），同时先提取列的唯一值集合，减少后续连接的冗余计算。

完整实现代码 #

-- 先提取目标列的所有唯一值，存为CTE
WITH unique_values AS (
    SELECT DISTINCT non_unique_column AS val
    FROM my_table
    WHERE non_unique_column IS NOT NULL -- 排除空值，避免无效组合
)
SELECT 
    RANK() OVER (ORDER BY 
        COALESCE(col1, '') + COALESCE(col2, '') + COALESCE(col3, '') + 
        COALESCE(col4, '') + COALESCE(col5, '')
    ) AS [index],
    col1, col2, col3, col4, col5
INTO final_comb_out
FROM (
    -- 1个值的组合
    SELECT val AS col1, NULL AS col2, NULL AS col3, NULL AS col4, NULL AS col5
    FROM unique_values

    UNION ALL

    -- 2个值的组合（保证val2 > val1，避免重复）
    SELECT 
        t1.val AS col1, 
        t2.val AS col2, 
        NULL AS col3, 
        NULL AS col4, 
        NULL AS col5
    FROM unique_values t1
    CROSS JOIN unique_values t2
    WHERE t2.val > t1.val

    UNION ALL

    -- 3个值的组合（val3 > val2 > val1）
    SELECT 
        t1.val AS col1, 
        t2.val AS col2, 
        t3.val AS col3, 
        NULL AS col4, 
        NULL AS col5
    FROM unique_values t1
    CROSS JOIN unique_values t2
    CROSS JOIN unique_values t3
    WHERE t2.val > t1.val AND t3.val > t2.val

    UNION ALL

    -- 4个值的组合（val4 > val3 > val2 > val1）
    SELECT 
        t1.val AS col1, 
        t2.val AS col2, 
        t3.val AS col3, 
        t4.val AS col4, 
        NULL AS col5
    FROM unique_values t1
    CROSS JOIN unique_values t2
    CROSS JOIN unique_values t3
    CROSS JOIN unique_values t4
    WHERE t2.val > t1.val AND t3.val > t2.val AND t4.val > t3.val

    UNION ALL

    -- 5个值的组合（val5 > val4 > val3 > val2 > val1）
    SELECT 
        t1.val AS col1, 
        t2.val AS col2, 
        t3.val AS col3, 
        t4.val AS col4, 
        t5.val AS col5
    FROM unique_values t1
    CROSS JOIN unique_values t2
    CROSS JOIN unique_values t3
    CROSS JOIN unique_values t4
    CROSS JOIN unique_values t5
    WHERE t2.val > t1.val AND t3.val > t2.val AND t4.val > t3.val AND t5.val > t4.val
) AS all_combinations
ORDER BY [index];

代码说明 #

• CTE unique_values：先提取目标列的所有非空唯一值，避免原表重复数据导致的冗余计算。
• 组合生成逻辑：通过WHERE子句强制后续值大于前一个值，确保每个无序组合只生成一次，替代LEAST/GREATEST的去重作用。
• 索引排序：用COALESCE处理NULL值，将组合拼接成字符串后排序生成索引，保证顺序稳定。
• 覆盖范围：包含1到5个值的所有唯一组合，符合需求。

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