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;
本文来自博客园,作者:Marydon,转载请注明原文链接:https://www.cnblogs.com/Marydon20170307/p/13153254.html