Oracle表多列值匹配查询：如何生成符合规则的两类输出结果

API 探路者

2026-10-21

解决方案

实现逻辑

先通过CTE定义所有待查询的指定值，再关联原表统计每行数据对目标值的匹配次数，最后按匹配次数拆分两个输出结果集。

完整SQL代码

首先替换代码中T_DATA为你的实际表名，若三个NUMBER列是数值类型，将search_values中查询值的单引号去掉即可：

-- 定义所有要检索的目标值
WITH search_values AS (
    SELECT '1234567' val FROM DUAL UNION ALL
    SELECT '9876543' val FROM DUAL UNION ALL
    SELECT '0616167' val FROM DUAL UNION ALL
    SELECT '1112223' val FROM DUAL UNION ALL
    SELECT '999999' val FROM DUAL
),
-- 统计每行匹配次数
match_calc AS (
    SELECT 
        t.*,
        s.val search_val,
        CASE WHEN t."NUMBER 1" = s.val THEN 1 ELSE 0 END 
        + CASE WHEN t."NUMBER 2" = s.val THEN 1 ELSE 0 END 
        + CASE WHEN t."NUMBER 3" = s.val THEN 1 ELSE 0 END match_cnt
    FROM search_values s
    LEFT JOIN T_DATA t 
        ON s.val IN (t."NUMBER 1", t."NUMBER 2", t."NUMBER 3")
)

-- Output1：仅匹配1次的行
SELECT ID, "NUMBER 1", "NUMBER 2", "NUMBER 3", LOC 
FROM match_calc 
WHERE match_cnt = 1;

-- Output2：多次匹配或无匹配的行
SELECT 
    NVL(ID, search_val) ID,
    "NUMBER 1",
    "NUMBER 2",
    "NUMBER 3",
    LOC,
    CASE 
        WHEN match_cnt >=2 THEN 'Multiple Match'
        ELSE 'No Match' 
    END Flag
FROM match_calc
WHERE match_cnt >=2 OR match_cnt = 0;