oracle 三表关联查询 202513034编辑

Heaven helps those who help themselves
资深码农+深耕理财=财富自由
欢迎关注

oracle 三表关联查询

Created by Marydon on 2018-07-04 17:55

左连接实现三表关联

表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';
复制代码

 

与君共勉:最实用的自律是攒钱,最养眼的自律是健身,最健康的自律是早睡,最改变气质的自律是看书,最好的自律是经济独立 。

您的一个点赞,一句留言,一次打赏,就是博主创作的动力源泉!

↓↓↓↓↓↓写的不错,对你有帮助?赏博主一口饭吧↓↓↓↓↓↓

posted @   Marydon  阅读(13034)  评论(0编辑  收藏  举报
编辑推荐:
· AI与.NET技术实操系列:使用Catalyst进行自然语言处理
· 分享一个我遇到过的“量子力学”级别的BUG。
· Linux系列:如何调试 malloc 的底层源码
· AI与.NET技术实操系列:基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
阅读排行:
· 历时 8 年,我冲上开源榜前 8 了!
· 物流快递公司核心技术能力-海量大数据处理技术
· 四大AI编程工具组合测评
· 关于能否用DeepSeek做危险的事情,DeepSeek本身给出了答案
· 如何在 Github 上获得 1000 star?
历史上的今天:
2017-07-04 chrome 谷歌浏览器插件损坏
2017-07-04 chrome 禁止自动更新
2017-07-04 chrome 如何官网下载谷歌浏览器离线安装包
2017-07-04 chrome打包程序
点击右上角即可分享
微信分享提示
sorry,本博客所有代码禁止复制,原创代码需扫码支付方可获取!
关闭

1、先加好友再付费,点我加好友;

2、代码不能满足你的需求?加好友付费定制你的专属代码!

3、付费标准及方式,点我查看详情。