oracle 三表关联查询 202513027编辑

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  阅读(13027)  评论(0编辑  收藏  举报
编辑推荐:
· Linux系列:如何调试 malloc 的底层源码
· AI与.NET技术实操系列:基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
阅读排行:
· 25岁的心里话
· 闲置电脑爆改个人服务器(超详细) #公网映射 #Vmware虚拟网络编辑器
· 因为Apifox不支持离线,我果断选择了Apipost!
· 零经验选手,Compose 一天开发一款小游戏!
· 通过 API 将Deepseek响应流式内容输出到前端
历史上的今天:
2017-07-04 chrome 谷歌浏览器插件损坏
2017-07-04 chrome 禁止自动更新
2017-07-04 chrome 如何官网下载谷歌浏览器离线安装包
2017-07-04 chrome打包程序
点击右上角即可分享
微信分享提示