Oracle表关联查询举例,oracle 左连接四表关联查询示例

1.情景展示

查询指定时间段内的某卡的每天的注册量

2.原因分析

使用group by按天进行分组查询，并将查询结果看做一张表；

connect by递归查询出指定时间段所横跨的日期作为主表，注册量作为辅表，与主表日期进行关联查询

3.解决方案

SELECT NVL(APP_ZS, 0) ONLINE_FORMAL, --‘‘转0

NVL(APP_LS, 0) ONLINE_INFORMAL,

NVL(JG_ZS, 0) OFFLINE_FORMAL,

NVL(JG_LS, 0) OFFLINE_INFORMAL,

D.REGDATE REGTIME

FROM (SELECT COUNT(1) APP_ZS, /*线上正式卡注册量*/

TO_CHAR(V.REGTIME, ‘YYYY-MM-DD‘) REGDATE

FROM VIRTUAL_CARD V, APP_ACCESS A

WHERE V.IDENTIFIER = A.ID

GROUP BY TO_CHAR(V.REGTIME, ‘YYYY-MM-DD‘)) ONLINE_FORMAL_CARD_REGIST,

(SELECT COUNT(1) APP_LS, /*线上临时卡注册量*/

TO_CHAR(V.REGTIME, ‘YYYY-MM-DD‘) REGDATE

FROM VIRTUAL_CARD_INTERIM V, APP_ACCESS A

WHERE V.IDENTIFIER = A.ID

GROUP BY TO_CHAR(V.REGTIME, ‘YYYY-MM-DD‘)) ONLINE_INFORMAL_CARD_REGIST,

(SELECT COUNT(1) JG_ZS, /*线下正式卡注册量*/

TO_CHAR(V.REGTIME, ‘YYYY-MM-DD‘) REGDATE

FROM VIRTUAL_CARD V, ORG_ACCESS O

WHERE V.IDENTIFIER = O.ID

GROUP BY TO_CHAR(V.REGTIME, ‘YYYY-MM-DD‘)) OFFLINE_FORMAL_CARD_REGIST,

(SELECT COUNT(1) JG_LS, /*线下临时卡注册量*/

TO_CHAR(V.REGTIME, ‘YYYY-MM-DD‘) REGDATE

FROM VIRTUAL_CARD_INTERIM V, ORG_ACCESS O

WHERE V.IDENTIFIER = O.ID

GROUP BY TO_CHAR(V.REGTIME, ‘YYYY-MM-DD‘)) OFFLINE_INFORMAL_CARD_REGIST,

/*左连接(以天为主表)*/

(SELECT TO_CHAR(TO_DATE(‘2020-06-01‘, ‘YYYY-MM-DD‘) + ROWNUM - 1,

‘YYYY-MM-DD‘) AS REGDATE

FROM DUAL

CONNECT BY ROWNUM <=

TRUNC(TO_DATE(‘2020-06-17‘, ‘YYYY-MM-DD‘) -

TO_DATE(‘2020-06-01‘, ‘YYYY-MM-DD‘)) + 1) D

WHERE D.REGDATE = ONLINE_FORMAL_CARD_REGIST.REGDATE(+)

AND D.REGDATE = ONLINE_INFORMAL_CARD_REGIST.REGDATE(+)

AND D.REGDATE = OFFLINE_FORMAL_CARD_REGIST.REGDATE(+)

AND D.REGDATE = OFFLINE_INFORMAL_CARD_REGIST.REGDATE(+)

ORDER BY D.REGDATE;

写在最后

哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

相关推荐：