Oracle 数据库中 FULL OUTER JOIN 的作用 ?
当需要同时显示两个表中所有记录时,FULL OUTER JOIN 就非常有用。
FULL OUTER JOIN 返回左表(Table A)和右表(Table B)的所有行,并且如果左表或右表中没有匹配的行,则使用 NULL 值填充缺失的部分。
例子:
SELECT
'' AS 唯一标识,
'0' AS 是否汇总,
'04' AS 数据粒度,
COALESCE(a.ORG_NO, b.ORG_NO) AS 单位编号,
NVL(a.WOKER_TOTAL_NUM, 0) AS 工人总数,
NVL(b.DENSO_NUM, 0) AS 电装表贴数量,
NVL(b.COMMISSION_BENCH_NUM, 0) AS 调试台数量,
NVL(b.VIBRATION_TABLE_NUM, 0) AS 振动台数量,
COALESCE(a.DATA_DATE, b.DATA_DATE, SYSDATE) AS 数据日期,
COALESCE(a.CREATE_TIME, b.CREATE_TIME, SYSDATE) AS 统计日期
FROM (
SELECT
ORG_NO,
WOKER_TOTAL_NUM,
DATA_DATE,
CREATE_TIME
FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY ORG_NO, TO_CHAR(DATA_DATE, 'YYYY') ORDER BY DATA_DATE DESC, CREATE_TIME DESC) rn,
ORG_NO,
NVL(WOKER_TOTAL_NUM, 0) AS WOKER_TOTAL_NUM,
DATA_DATE,
CREATE_TIME
FROM BRAIN.DS_WOKER_WORK_HOUR_INFO
) a
WHERE a.rn = 1
) a
FULL OUTER JOIN (
SELECT
ORG_NO,
DENSO_NUM,
COMMISSION_BENCH_NUM,
VIBRATION_TABLE_NUM,
DATA_DATE,
CREATE_TIME
FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY ORG_NO, TO_CHAR(DATA_DATE, 'YYYY') ORDER BY DATA_DATE DESC, CREATE_TIME DESC) rn,
ORG_NO,
NVL(DENSO_NUM, 0) AS DENSO_NUM,
NVL(COMMISSION_BENCH_NUM, 0) AS COMMISSION_BENCH_NUM,
NVL(VIBRATION_TABLE_NUM, 0) AS VIBRATION_TABLE_NUM,
DATA_DATE,
CREATE_TIME
FROM BRAIN.DS_PRODUCT_EQUIP_INFO
) b
WHERE b.rn = 1
) b
ON a.ORG_NO = b.ORG_NO AND TO_CHAR(a.DATA_DATE,'YYYY') = TO_CHAR(NVL(b.DATA_DATE, SYSDATE),'YYYY')
a表执行结果:
b表执行结果:
FULL OUTER JOIN 关联之后的结果:(a表多了一条:2021 年的数据,b表没有) ,(b表多了一条:2028年的数据 ,a表没有) 。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步