Oracle SQL需求：为参数值组合生成分组标识Set

通用方案（适配任意数量的Param ID和Param Val） #

该方法通过生成参数值的笛卡尔积映射关系，自动计算对应的Set编号，适合参数数量较多的场景：

WITH param_ranks AS (
    -- 为每个Param ID下的不同Param Val分配唯一排名
    SELECT DISTINCT 
        Param_ID,
        Param_Val,
        DENSE_RANK() OVER (PARTITION BY Param_ID ORDER BY Param_Val) AS val_rank
    FROM your_table
),
set_mappings AS (
    -- 生成所有Param ID的参数值组合，并计算对应的Set编号
    SELECT 
        -- 公式逻辑：(Param ID1的排名-1)*Param ID2的参数值数量 + Param ID2的排名
        (pr1.val_rank - 1) * (SELECT COUNT(DISTINCT Param_Val) FROM param_ranks WHERE Param_ID=2) + pr2.val_rank AS Set,
        pr1.Param_ID AS p1_id,
        pr1.Param_Val AS p1_val,
        pr2.Param_ID AS p2_id,
        pr2.Param_Val AS p2_val
    FROM param_ranks pr1
    CROSS JOIN param_ranks pr2
    WHERE pr1.Param_ID = 1 AND pr2.Param_ID = 2
)
-- 关联原表与映射关系，得到最终结果
SELECT 
    sm.Set,
    t.Param_ID,
    t.Param_Val,
    t.Other_Cols
FROM your_table t
JOIN set_mappings sm 
    ON (t.Param_ID = sm.p1_id AND t.Param_Val = sm.p1_val)
    OR (t.Param_ID = sm.p2_id AND t.Param_Val = sm.p2_val)
ORDER BY t.Param_ID, t.Param_Val, sm.Set;

逻辑说明：

1. param_ranks子查询：用DENSE_RANK()为每个Param ID下的不同Param Val生成连续排名，比如Param ID1的15对应1、16对应2；Param ID2的21对应1、22对应2。
2. set_mappings子查询：通过笛卡尔积生成Param ID1和Param ID2的所有参数值组合，用公式计算唯一的Set编号（确保每个组合对应唯一Set）。
3. 最后将原表与映射表关联，匹配每条记录对应的Set值。

固定参数方案（适合已知参数数量和值的场景） #

如果Param ID和Param Val的数量固定，可以直接定义规则映射，写法更简洁：

WITH ranked_rows AS (
    -- 为每个Param ID+Param Val下的重复记录分配行号
    SELECT 
        *,
        DENSE_RANK() OVER (PARTITION BY Param_ID ORDER BY Param_Val) AS val_rank,
        ROW_NUMBER() OVER (PARTITION BY Param_ID, Param_Val ORDER BY (SELECT NULL)) AS row_num
    FROM your_table
),
set_rules AS (
    -- 直接定义每个参数值+行号对应的Set编号
    SELECT 1 AS Param_ID, 1 AS val_rank, 1 AS row_num, 1 AS Set UNION ALL
    SELECT 1,1,2,2 UNION ALL
    SELECT 1,2,1,3 UNION ALL
    SELECT 1,2,2,4 UNION ALL
    SELECT 2,1,1,1 UNION ALL
    SELECT 2,1,2,3 UNION ALL
    SELECT 2,2,1,2 UNION ALL
    SELECT 2,2,2,4
)
-- 关联规则表得到结果
SELECT 
    sr.Set,
    rr.Param_ID,
    rr.Param_Val,
    rr.Other_Cols
FROM ranked_rows rr
JOIN set_rules sr 
    ON rr.Param_ID = sr.Param_ID 
    AND rr.val_rank = sr.val_rank 
    AND rr.row_num = sr.row_num
ORDER BY rr.Param_ID, rr.Param_Val, sr.Set;

逻辑说明：

1. ranked_rows子查询：先用DENSE_RANK()标记参数值的排名，再用ROW_NUMBER()标记同参数值下的重复记录序号（区分两条相同的Param Val记录）。
2. set_rules子查询：根据期望的Set对应关系，直接定义每个参数值+行号对应的Set编号。
3. 关联后即可得到符合要求的结果。

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