oracle 三表關聯查詢

標籤：nio   sql   com   trouble   hds   font   author   column   oracl   

 oracle 三表關聯查詢CreationTime--2018年7月4日17點52分Author:Marydon

左串連實現三表關聯

表A---------------------------------關聯第一張表B-----------------------關聯第二張表c

1.文法

　　select * from 表名A left join 表B on A.columnX=B.columnM and A.columnY=B.columnN left join 表c on 表A=表c的id

2.應用情境

　　四張表 GJPT_BASY、GJZY_BASY、GJPT_BASY_ERROR、GJZY_BASY_ERROR
　　根據四張表，要求返回：醫學機構名稱，醫學機構編號，總數，合格數和問題數

3.SQL實現

SELECT TEMP1.*, TEMP2.HEGESUM, TEMP3.TROUBLESUM  FROM (SELECT T1.YLNAME, T1.YLCODE, SUM(RS1) AS YLSUM--總數      FROM (SELECT COUNT(1) AS RS1,               HDSD00_11_118 AS YLNAME,               HDSD00_11_119 AS YLCODE          FROM GJPT_BASY         GROUP BY HDSD00_11_119, HDSD00_11_118        UNION ALL        SELECT COUNT(1) AS RS1,               HDSD00_12_133 AS YLNAME,               HDSD00_12_134 AS YLCODE          FROM GJZY_BASY         GROUP BY HDSD00_12_133, HDSD00_12_134        UNION ALL        SELECT COUNT(1) AS RS1,               HDSD00_11_118 AS YLNAME,               HDSD00_11_119 AS YLCODE          FROM GJPT_BASY_ERROR         GROUP BY HDSD00_11_119, HDSD00_11_118        UNION ALL        SELECT COUNT(1) AS RS1,               HDSD00_12_133 AS YLNAME,               HDSD00_12_134 AS YLCODE          FROM GJZY_BASY_ERROR         GROUP BY HDSD00_12_133, HDSD00_12_134) T1     GROUP BY T1.YLNAME, T1.YLCODE) TEMP1  LEFT JOIN (SELECT *           FROM (SELECT T2.YLNAME, T2.YLCODE, SUM(RS2) AS HEGESUM--合格數               FROM (SELECT COUNT(1) AS RS2,                    HDSD00_11_118 AS YLNAME,                    HDSD00_11_119 AS YLCODE                   FROM GJPT_BASY                  GROUP BY HDSD00_11_119, HDSD00_11_118                 UNION ALL                 SELECT COUNT(1) AS RS2,                    HDSD00_12_133 AS YLNAME,                    HDSD00_12_134 AS YLCODE                   FROM GJZY_BASY                  GROUP BY HDSD00_12_133, HDSD00_12_134) T2              GROUP BY T2.YLNAME, T2.YLCODE)) TEMP2    ON TEMP2.YLNAME = TEMP1.YLNAME   AND TEMP2.YLCODE = TEMP1.YLCODE  LEFT JOIN (SELECT *           FROM (SELECT T3.YLNAME, T3.YLCODE, SUM(RS3) TROUBLESUM--問題數               FROM (SELECT COUNT(1) AS RS3,                    HDSD00_11_118 AS YLNAME,                    HDSD00_11_119 AS YLCODE                   FROM GJPT_BASY_ERROR                  GROUP BY HDSD00_11_119, HDSD00_11_118                 UNION ALL                 SELECT COUNT(1) AS RS3,                    HDSD00_12_133 AS YLNAME,                    HDSD00_12_134 AS YLCODE                   FROM GJZY_BASY_ERROR                  GROUP BY HDSD00_12_133, HDSD00_12_134) T3              GROUP BY T3.YLNAME, T3.YLCODE)) TEMP3    ON TEMP3.YLNAME = TEMP1.YLNAME   AND TEMP3.YLCODE = TEMP1.YLCODE   WHERE TEMP3.YLCODE=‘41580781841010511A1001‘;

 

 

 相關推薦：

• oracle 兩表關聯查詢

 

 

oracle 三表關聯查詢