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

最新推荐文章于 2023-09-11 10:56:31 发布

头大如钟

最新推荐文章于 2023-09-11 10:56:31 发布

阅读量1.8k

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/qq_39618582/article/details/106408319

版权

oracle 专栏收录该内容

2 篇文章 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