以日期GROUP BY分組查詢并作為條件關聯3個子查詢,子查詢分别對應T1/T2/T3,日期是唯一關聯條件,一環扣一環,但是如果出現其中某個表沒有某一天的資料,而其他表有,比如某一天T1、T3有結果值,但是T2是個空集,鍊條就斷了,就會同一天出現兩條資料即重複日期的結果。這時隻要在查詢結果外面再加一層查詢(如紅色标記),就能很好的解決FULL OUTER JOIN語句的去重問題了!
SELECT substr(st.sdd,0,8),sum(st.sbks),sum(st.sbs),sum(st.sje),sum(st.sjc)
FROM ( SELECT
(T1.dd||T2.dd||T3.dd) sdd, T1.bks sbks, T2.bs sbs, T2.je sje, T3.jc sjc
FROM ( SELECT
create_date dd, COUNT(*) bks
FROM TBL_TCPAY WHERE
create_date >='20180806' AND create_date<='20180816'
AND status = '12' GROUP BY create_date ORDER BY 1) T1
FULL OUTER JOIN
( SELECT
localdate dd, COUNT(*) bs, SUM(amount)/100 je
FROM TBL_ATPAY WHERE
localdate >='20180801' AND localdate <='20180905'
AND status = '0' GROUP BY localdate ORDER BY 1) T2
ON T1.dd = T2.dd
FULL OUTER JOIN
( SELECT
bind_date dd , COUNT(*) jc
FROM TBL_COMPAY WHERE
bind_date >='20180806' AND bind_date <='20180816'
AND comresource='2' GROUP BY bind_date ORDER BY 1 ) T3
ON T2.dd = T3.dd ORDER BY 1 ) st
GROUP BY substr(st.sdd,0,8) ORDER BY 1;