代码之家 › 专栏 › 技术社区 › C_B

将两个Oracle SQL查询与四个表中的一个左外部联接和两个内部联接结合起来

outer-join inner-join oracle sql

C_B · 技术社区 · 8 年前

SELECT distinct condition as condition_code, value as condition_literal
FROM CULVERT_INSPECT
   LEFT OUTER JOIN
   (SELECT EXTRACTVALUE (CodedValues.COLUMN_VALUE, 'CodedValue/Code')
              AS Code,
           EXTRACTVALUE (CodedValues.COLUMN_VALUE, 'CodedValue/Name')
              AS VALUE
      FROM GDB_ITEMS_VW items
           INNER JOIN GDB_ITEMTYPES itemtypes
              ON items.TYPE = itemtypes.UUID,
           TABLE (
              XMLSEQUENCE (
                 XMLType (Definition).EXTRACT (
                    '/GPCodedValueDomain2/CodedValues/CodedValue'))) 
 CodedValues
     WHERE itemtypes.Name = 'Coded Value Domain'
           AND items.Name = 'OVERALL_CONDITION') coded_values
             on CULVERT_INSPECT.condition = coded_values.code
 ORDER BY CONDITION_CODE

第二个查询:

SELECT distinct CULVERT_INSPECT.CONDITION
FROM CULVERTS
   INNER JOIN CULVERT_INSPECT ON (CULVERTS.GLOBALID_1 = 
CULVERTGID)
WHERE COST_CENTER = '551807'

第一个查询的输出:

CONDITION_CODE  CONDITION_LITERAL
0               Excellent - Like New
1               Good - Some Wear, Structurally Sound
2               Poor - Deteriorated, Consider For Repair
3               Very Poor - Serious Deterioration
4               Inaccessible

1 回复 | 直到 8 年前

Kaushik Nayak 8 年前

使用 with clause . 但由于没有样本数据,因此未经测试。

WITH cons
         AS (SELECT DISTINCT culvertgid , condition AS condition_code,
                             value     AS condition_literal
             FROM   culvert_inspect
                    LEFT OUTER JOIN (SELECT Extractvalue (CodedValues.column_value,
                                            'CodedValue/Code') AS Code,
                                            Extractvalue (CodedValues.column_value,
                                            'CodedValue/Name') AS VALUE
                                     FROM   gdb_items_vw items
                                            inner join gdb_itemtypes itemtypes
                                                    ON items.TYPE = itemtypes.uuid,
                                            TABLE (
            Xmlsequence (Xmltype (DEFINITION).EXTRACT (
                         '/GPCodedValueDomain2/CodedValues/CodedValue')))
                            CodedValues
                     WHERE  itemtypes.name = 'Coded Value Domain'
                            AND items.name = 'OVERALL_CONDITION')
                    coded_values
                 ON culvert_inspect.condition = coded_values.code)
    SELECT DISTINCT ci.condition_code
    FROM   culverts c
           INNER JOIN  cons ci
                   ON ( c.globalid_1 = ci.culvertgid )
    WHERE  cost_center = '551807'
    ORDER  BY condition_code;