Oracle中如何获取两个查询结果中共有的RJ_SPAN_ID？

下面提供三种常用的Oracle实现方式，按需选择：

方法1：使用INTERSECT关键字 #

INTERSECT会直接返回两个查询结果中完全匹配的行，适合只需要共同SPAN_ID且自动去重的场景：

SELECT TO_CHAR(TRIM(RJ_SPAN_ID)) AS RJ_SPAN_ID
FROM NE.MV_SPAN@NE
WHERE 
  LENGTH(TRIM(RJ_SPAN_ID)) = 21
  AND REGEXP_LIKE(TRIM(RJ_SPAN_ID), 'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$','i')                                  
  AND NOT REGEXP_LIKE (NVL(RJ_INTRACITY_LINK_ID,'-'),'_(9)','i') 
  AND INVENTORY_STATUS_CODE = 'IPL'
  AND RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE

INTERSECT

SELECT TO_CHAR(TRIM(RJ_SPAN_ID)) AS RJ_SPAN_ID
FROM NE.MV_TRANSMEDIA@NE
WHERE 
  INVENTORY_STATUS_CODE = 'IPL'
  AND LENGTH(TRIM(RJ_SPAN_ID)) = 21
  AND REGEXP_LIKE(TRIM(RJ_SPAN_ID), 'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$','i')
  AND NOT REGEXP_LIKE (NVL(RJ_INTRACITY_LINK_ID,'-'),'_(9)','i')
  AND RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE;

注意：统一两个查询的输出字段处理（都用TO_CHAR(TRIM(RJ_SPAN_ID))），避免因空格或数据类型差异导致匹配失败。

方法2：使用IN子查询 #

如果需要保留原查询1中的其他字段（比如MAINT_ZONE_CODE），用IN子查询更直接：

SELECT TO_CHAR(TRIM(RJ_SPAN_ID)) AS RJ_SPAN_ID,
       TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE 
FROM NE.MV_SPAN@NE
WHERE 
  LENGTH(TRIM(RJ_SPAN_ID)) = 21
  AND REGEXP_LIKE(TRIM(RJ_SPAN_ID), 'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$','i')                                  
  AND NOT REGEXP_LIKE (NVL(RJ_INTRACITY_LINK_ID,'-'),'_(9)','i') 
  AND INVENTORY_STATUS_CODE = 'IPL'
  AND RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE
  AND TO_CHAR(TRIM(RJ_SPAN_ID)) IN (
    SELECT TO_CHAR(TRIM(RJ_SPAN_ID))
    FROM NE.MV_TRANSMEDIA@NE
    WHERE 
      INVENTORY_STATUS_CODE = 'IPL'
      AND LENGTH(TRIM(RJ_SPAN_ID)) = 21
      AND REGEXP_LIKE(TRIM(RJ_SPAN_ID), 'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$','i')
      AND NOT REGEXP_LIKE (NVL(RJ_INTRACITY_LINK_ID,'-'),'_(9)','i')
      AND RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE
  );

方法3：使用JOIN关联查询 #

如果需要同时获取两个表中的相关字段，用JOIN关联更灵活：

SELECT DISTINCT TO_CHAR(TRIM(s.RJ_SPAN_ID)) AS RJ_SPAN_ID
FROM NE.MV_SPAN@NE s
JOIN NE.MV_TRANSMEDIA@NE tm
  ON TO_CHAR(TRIM(s.RJ_SPAN_ID)) = TO_CHAR(TRIM(tm.RJ_SPAN_ID))
WHERE 
  -- MV_SPAN表过滤条件
  LENGTH(TRIM(s.RJ_SPAN_ID)) = 21
  AND REGEXP_LIKE(TRIM(s.RJ_SPAN_ID), 'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$','i')                                  
  AND NOT REGEXP_LIKE (NVL(s.RJ_INTRACITY_LINK_ID,'-'),'_(9)','i') 
  AND s.INVENTORY_STATUS_CODE = 'IPL'
  AND s.RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE
  -- MV_TRANSMEDIA表过滤条件
  AND tm.INVENTORY_STATUS_CODE = 'IPL'
  AND LENGTH(TRIM(tm.RJ_SPAN_ID)) = 21
  AND REGEXP_LIKE(TRIM(tm.RJ_SPAN_ID), 'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$','i')
  AND NOT REGEXP_LIKE (NVL(tm.RJ_INTRACITY_LINK_ID,'-'),'_(9)','i')
  AND tm.RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE;

使用DISTINCT避免因关联产生重复结果，若需要其他字段，直接在SELECT中添加即可（比如tm.RJ_MAINTENANCE_ZONE_CODE）。

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