如何在Oracle中编写SQL查询实现同表元素按序配对查询

我需要从parameters表中，将两个数组的对应位置元素配对返回（第一个数组第N个元素对应第二个数组第N个元素），但当前的笛卡尔积查询返回了所有可能的组合，不是预期的按位置配对结果。

错误查询示例 #

SELECT
    p1.parameter_name,
    p2.parameter_name
FROM
    parameters p1,
    parameters p2
WHERE
    p1.parameter_name IN ( 'NOM', 'NOM', 'SALARY', 'RRRR', 'PSC3',
                           'PSC9H50' )
    AND p2.parameter_name IN ( 'AGE', 'ZZZ4', 'SDFGH', 'CDA', 'SD',
                               'KIY' );

预期结果 #

解决方案 #

方案1：MySQL（8.0+支持窗口函数） #

通过生成带行号的临时数据集，将两个数组的元素按位置关联：

WITH params_a AS (
    SELECT param, ROW_NUMBER() OVER () AS rn
    FROM (
        SELECT 'NOM' AS param UNION ALL
        SELECT 'NOM' UNION ALL
        SELECT 'SALARY' UNION ALL
        SELECT 'RRRR' UNION ALL
        SELECT 'PSC3' UNION ALL
        SELECT 'PSC9H50'
    ) t
), params_b AS (
    SELECT param, ROW_NUMBER() OVER () AS rn
    FROM (
        SELECT 'AGE' AS param UNION ALL
        SELECT 'ZZZ4' UNION ALL
        SELECT 'SDFGH' UNION ALL
        SELECT 'CDA' UNION ALL
        SELECT 'SD' UNION ALL
        SELECT 'KIY'
    ) t
)
SELECT pa.param AS ParamA, pb.param AS ParamB
FROM params_a pa
JOIN params_b pb ON pa.rn = pb.rn
JOIN parameters p1 ON p1.parameter_name = pa.param
JOIN parameters p2 ON p2.parameter_name = pb.param;

如果只需要数组元素按位置配对，无需校验parameters表中是否存在对应数据，可以去掉最后的两个JOIN。

方案2：PostgreSQL #

利用unnest结合WITH ORDINALITY直接生成带位置索引的数组元素：

SELECT pa.param AS ParamA, pb.param AS ParamB
FROM unnest(ARRAY['NOM', 'NOM', 'SALARY', 'RRRR', 'PSC3', 'PSC9H50']) WITH ORDINALITY AS pa(param, rn)
JOIN unnest(ARRAY['AGE', 'ZZZ4', 'SDFGH', 'CDA', 'SD', 'KIY']) WITH ORDINALITY AS pb(param, rn) ON pa.rn = pb.rn
JOIN parameters p1 ON p1.parameter_name = pa.param
JOIN parameters p2 ON p2.parameter_name = pb.param;

方案3：SQL Server #

使用OPENJSON生成带索引的数组元素，再关联：

WITH params_a AS (
    SELECT value AS param, CAST([key] AS INT) + 1 AS rn
    FROM OPENJSON('["NOM", "NOM", "SALARY", "RRRR", "PSC3", "PSC9H50"]')
), params_b AS (
    SELECT value AS param, CAST([key] AS INT) + 1 AS rn
    FROM OPENJSON('["AGE", "ZZZ4", "SDFGH", "CDA", "SD", "KIY"]')
)
SELECT pa.param AS ParamA, pb.param AS ParamB
FROM params_a pa
JOIN params_b pb ON pa.rn = pb.rn
JOIN parameters p1 ON p1.parameter_name = pa.param
JOIN parameters p2 ON p2.parameter_name = pb.param;

核心逻辑都是给两个数组的每个元素分配对应的位置编号，再通过编号关联，避免笛卡尔积，实现按位置配对。

内容的提问来源于stack exchange，提问作者Mohammed Housseyn Taleb