oracle 三表关联查询

本文由Marydon撰写,介绍了Oracle中使用左连接进行三表关联查询的语法和应用场景,并提供了一个具体的SQL实现例子,涉及GJPT_BASY、GJZY_BASY、GJPT_BASY_ERROR、GJZY_BASY_ERROR四张表的数据整合。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

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';

 

 

 相关推荐:

 

 
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值