基于SPAN_ID连接不同Schema的两个Oracle查询实现数据查询

我有两个Oracle查询语句，需要基于SPAN_ID字段将它们关联，实现跨不同Schema的数据查询。以下是原始查询语句：

原始查询1 #

ELSIF PSPANTYPE = 'INTRACITY' THEN
          BEGIN
            OPEN PSPANDATA FOR
           
             SELECT T.SPAN_ID,MAINT_ZONE_CODE,identify_valid_invalid(T.SPAN_ID) as VALIDINFO
              FROM (
                SELECT TO_CHAR(TRIM(RJ_INTRACITY_LINK_ID)) AS SPAN_ID, TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS  MAINT_ZONE_CODE
                FROM NE.MV_SPAN@DB_LINK_NE_VIEWER
              --   FROM  APP_FTTX.span@SAT
                WHERE LENGTH(trim(RJ_INTRACITY_LINK_ID)) > 8 
                AND LENGTH(trim(RJ_INTRACITY_LINK_ID)) < 21            
                AND INVENTORY_STATUS_CODE = 'IPL' 
                AND RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE
                AND NOT REGEXP_LIKE (NVL(TRIM(RJ_INTRACITY_LINK_ID),'-'),'_(9|31|4|7)|(_U)$|(/)$','i')
              MINUS         
              SELECT TO_CHAR(LINK_ID) AS SPAN_ID,TO_CHAR(MAINTENANCEZONECODE) AS MAINT_ZONE_CODE
                  FROM TBL_FIBER_INV_JOBS
                  WHERE SPAN_TYPE = 'INTRACITY'
                  AND MAINTENANCEZONECODE = PMAINTZONECODE
                  ORDER BY 1)T
                  order by 3 desc;
          END;

原始查询2 #

select * from APP_LCO.tbl_fip_checklist where spanid in  ('HRFAHBHRODHNSPN001_BU','MHAGBMMHTLVDSPN001_BU','MHBIDKMHAGBMSPN009_BU','MHNVSAMHAGBMSPN005_BU')
and status = 'APPROVED';

关联后的查询方案 #

你可以通过JOIN操作将两个查询的结果基于SPAN_ID（查询1）和SPANID（查询2）关联起来，以下是两种常用关联方式，按需选择：

方式1：内连接（仅保留两边匹配的记录）

仅返回同时存在于查询1结果和查询2结果中的记录：

ELSIF PSPANTYPE = 'INTRACITY' THEN
          BEGIN
            OPEN PSPANDATA FOR
             SELECT 
               T.SPAN_ID, 
               T.MAINT_ZONE_CODE, 
               identify_valid_invalid(T.SPAN_ID) as VALIDINFO,
               c.* -- 建议替换为具体字段，避免冗余
             FROM (
                SELECT TO_CHAR(TRIM(RJ_INTRACITY_LINK_ID)) AS SPAN_ID, TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS  MAINT_ZONE_CODE
                FROM NE.MV_SPAN@DB_LINK_NE_VIEWER
              --   FROM  APP_FTTX.span@SAT
                WHERE LENGTH(trim(RJ_INTRACITY_LINK_ID)) > 8 
                AND LENGTH(trim(RJ_INTRACITY_LINK_ID)) < 21            
                AND INVENTORY_STATUS_CODE = 'IPL' 
                AND RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE
                AND NOT REGEXP_LIKE (NVL(TRIM(RJ_INTRACITY_LINK_ID),'-'),'_(9|31|4|7)|(_U)$|(/)$','i')
              MINUS         
              SELECT TO_CHAR(LINK_ID) AS SPAN_ID,TO_CHAR(MAINTENANCEZONECODE) AS MAINT_ZONE_CODE
                  FROM TBL_FIBER_INV_JOBS
                  WHERE SPAN_TYPE = 'INTRACITY'
                  AND MAINTENANCEZONECODE = PMAINTZONECODE
                  ORDER BY 1)T
             INNER JOIN APP_LCO.tbl_fip_checklist c
               ON T.SPAN_ID = c.SPANID
             WHERE c.status = 'APPROVED'
               AND c.spanid IN ('HRFAHBHRODHNSPN001_BU','MHAGBMMHTLVDSPN001_BU','MHBIDKMHAGBMSPN009_BU','MHNVSAMHAGBMSPN005_BU')
             ORDER BY VALIDINFO desc;
          END;

方式2：左连接（保留查询1的所有记录，匹配查询2数据）

保留查询1的全部结果，匹配到查询2的记录则显示对应数据，无匹配则显示NULL：

ELSIF PSPANTYPE = 'INTRACITY' THEN
          BEGIN
            OPEN PSPANDATA FOR
             SELECT 
               T.SPAN_ID, 
               T.MAINT_ZONE_CODE, 
               identify_valid_invalid(T.SPAN_ID) as VALIDINFO,
               c.* -- 建议替换为具体字段，避免冗余
             FROM (
                SELECT TO_CHAR(TRIM(RJ_INTRACITY_LINK_ID)) AS SPAN_ID, TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS  MAINT_ZONE_CODE
                FROM NE.MV_SPAN@DB_LINK_NE_VIEWER
              --   FROM  APP_FTTX.span@SAT
                WHERE LENGTH(trim(RJ_INTRACITY_LINK_ID)) > 8 
                AND LENGTH(trim(RJ_INTRACITY_LINK_ID)) < 21            
                AND INVENTORY_STATUS_CODE = 'IPL' 
                AND RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE
                AND NOT REGEXP_LIKE (NVL(TRIM(RJ_INTRACITY_LINK_ID),'-'),'_(9|31|4|7)|(_U)$|(/)$','i')
              MINUS         
              SELECT TO_CHAR(LINK_ID) AS SPAN_ID,TO_CHAR(MAINTENANCEZONECODE) AS MAINT_ZONE_CODE
                  FROM TBL_FIBER_INV_JOBS
                  WHERE SPAN_TYPE = 'INTRACITY'
                  AND MAINTENANCEZONECODE = PMAINTZONECODE
                  ORDER BY 1)T
             LEFT JOIN APP_LCO.tbl_fip_checklist c
               ON T.SPAN_ID = c.SPANID
               AND c.status = 'APPROVED'
               AND c.spanid IN ('HRFAHBHRODHNSPN001_BU','MHAGBMMHTLVDSPN001_BU','MHBIDKMHAGBMSPN009_BU','MHNVSAMHAGBMSPN005_BU')
             ORDER BY VALIDINFO desc;
          END;

注意事项 #

• 若SPAN_ID和SPANID字段存在大小写差异，可通过UPPER()或LOWER()统一格式，比如ON UPPER(T.SPAN_ID) = UPPER(c.SPANID)。
• 避免使用SELECT *，明确指定所需字段，提升查询效率和代码可读性。
• 跨DB_LINK查询时，需确保DB_LINK连接正常且权限配置正确。

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