oracle按年月日统计，无数据补0

年

select b.DAY_TIME xaxis, nvl(a.yaxis,0) yaxis from ( select substr(o.CREATEDATE, 1,4) xaxis, count(*) yaxis from T_YY_ORDERDETAIL od inner join T_YY_ORDER o on od.ORDERID=o.ORDERID inner join T_YY_SERVICEINTERFACE sif on sif.ID=od.SERVICEID WHERE o.ORDERSERVICETYPE=7 group by substr(o.CREATEDATE, 1,4)) a

//上面是正常的统计，下面是处理日期，日期处理完成后用right join连接，然后用nvl将无数据的设置为0

right join (SELECT TO_CHAR(add_months(TO_DATE((select min(substr(o.CREATEDATE, 1, 4)) xaxis from T_YY_ORDER o), 'YYYY'),(ROWNUM - 1)*12),'YYYY') DAY_TIME FROM DUAL CONNECT BY (months_between(TO_DATE(?, 'YYYY'),TO_DATE((select min(substr(o.CREATEDATE, 1, 4)) xaxis from T_YY_ORDER o), 'YYYY')))/12 >= ROWNUM-1 ) b on a.xaxis= b.DAY_TIME order by b.DAY_TIME asc 

月：

select b.DAY_TIME xaxis, nvl(a.yaxis,0) yaxis from ( select substr(o.CREATEDATE, 1,7) xaxis, count(*) yaxis from T_YY_ORDERDETAIL od inner join T_YY_ORDER o on od.ORDERID=o.ORDERID inner join T_YY_SERVICEINTERFACE sif on sif.ID=od.SERVICEID WHERE o.ORDERSERVICETYPE=7 group by substr(o.CREATEDATE, 1,7)) a

//上面是正常的统计，下面是处理日期，日期处理完成后用right join连接，然后用nvl将无数据的设置为0

right join (SELECT TO_CHAR(add_months(TO_DATE((select min(substr(o.CREATEDATE, 1, 7)) xaxis from T_YY_ORDER o), 'YYYY-MM'), (ROWNUM - 1)), 'YYYY-MM') DAY_TIME FROM DUAL CONNECT BY (months_between(TO_DATE(?, 'YYYY-MM'),TO_DATE((select min(substr(o.CREATEDATE, 1, 7)) xaxis from T_YY_ORDER o), 'YYYY-MM'))) >= ROWNUM-1 ) b on a.xaxis= b.DAY_TIME order by b.DAY_TIME asc 
 

日：

select b.DAY_TIME xaxis, nvl(a.yaxis,0) yaxis from ( select substr(o.CREATEDATE, 1,10) xaxis, count(*) yaxis from T_YY_ORDERDETAIL od inner join T_YY_ORDER o on od.ORDERID=o.ORDERID inner join T_YY_SERVICEINTERFACE sif on sif.ID=od.SERVICEID WHERE o.ORDERSERVICETYPE=7 group by substr(o.CREATEDATE, 1,10)) a

//上面是正常的统计，下面是处理日期，日期处理完成后用right join连接，然后用nvl将无数据的设置为0

right join (SELECT TO_CHAR(TO_DATE((select min(substr(o.CREATEDATE, 1, 10)) xaxis from T_YY_ORDER o), 'YYYY-MM-DD') + ROWNUM - 1, 'YYYY-MM-DD') DAY_TIME FROM DUAL CONNECT BY (TO_DATE(?, 'YYYY-MM-DD') - TO_DATE((select min(substr(o.CREATEDATE, 1, 10)) xaxis from T_YY_ORDER o), 'YYYY-MM-DD')) >= ROWNUM ) b on a.xaxis= b.DAY_TIME order by b.DAY_TIME asc