ORACLE 月份不全,补全月份的sql


select a.month, nvl(b.amount,0) amount
--decode(b.amount,'',0)
from (select to_char(sysdate, 'yyyy') || lpad(level, 2, 0) month
from dual
connect by level < 13) a,
(select to_char(((sum(t.txn_amt) + sum(t.coupon_amt)) / 100),
'FM999,999,999,990.90') amount,
substr(t.inst_date, 0, 6) month
from t_log_acct_auth t
where t.confirm_flag = 'Y'
and t.cancel_flag = 'N'
and t.return_flag = 'N'
and substr(t.inst_date, 0, 4) = to_char(sysdate, 'yyyy')
group by substr(t.inst_date, 0, 6)) b

where a.month = b.month(+) order by a.month

原文地址:https://www.cnblogs.com/yangy608/p/2805575.html