统计经典SQL(构建日期补0)-创新互联

SELECT A.DAYLIST, NVL(B.MBC, 0) MBC
,CAST(NVL(B.SC, 0) AS NUMBER(10, 2)) SC
FROM (
SELECT TO_DATE(TO_CHAR(TO_DATE('2018-04-01', 'YYYY-MM-DD') + ROWNUM - 1, 'YYYY-MM-DD'), 'YYYY-MM-DD') AS DAYLIST
FROM DUAL
CONNECT BY ROWNUM <= TRUNC(TO_DATE('2018-04-30', 'YYYY-MM-DD') - TO_DATE('2018-04-01', 'YYYY-MM-DD')) + 1
) A
LEFT JOIN (
SELECT CREATEDATE, COUNT() AS MBC, 23.80 COUNT(*) AS SC
FROM (
SELECT MONEYBOX, TRUNC(CREATEDATE, 'DD') AS CREATEDATE
FROM TB_CASH C
WHERE C.ORGANNO IN (
SELECT O.ORGANNO
FROM TB_ORGAN O
START WITH O.ORGANNO = '00000028'
CONNECT BY PRIOR O.ORGANNO = O.PARENTORGANNO
AND O.RECORDSTATUS = '1'
)
GROUP BY C.MONEYBOX, TRUNC(CREATEDATE, 'DD')
HAVING TRUNC(CREATEDATE, 'DD') >= TO_DATE('2018-04-01', 'YYYY-MM-DD')
AND TRUNC(CREATEDATE, 'DD') <= TO_DATE('2018-04-30', 'YYYY-MM-DD')
) T
GROUP BY T.CREATEDATE
) B ON A.DAYLIST = B.CREATEDATE
ORDER BY DAYLIST

专注于为中小企业提供成都网站设计、网站建设服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业秦皇岛免费做网站提供优质的服务。我们立足成都，凝聚了一批互联网行业人才，有力地推动了1000多家企业的稳健成长，帮助中小企业通过网站建设实现规模扩充和转变。

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

当前题目：统计经典SQL(构建日期补0)-创新互联
转载注明：http://bzwzjz.com/article/eogge.html