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';
相关推荐:
与君共勉:最实用的自律是攒钱,最养眼的自律是健身,最健康的自律是早睡,最改变气质的自律是看书,最好的自律是经济独立 。
您的一个点赞,一句留言,一次打赏,就是博主创作的动力源泉!
↓↓↓↓↓↓写的不错,对你有帮助?赏博主一口饭吧↓↓↓↓↓↓
本文来自博客园,作者:Marydon,转载请注明原文链接:https://www.cnblogs.com/Marydon20170307/p/9264551.html
【推荐】还在用 ECharts 开发大屏?试试这款永久免费的开源 BI 工具!
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 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打包程序