大橙子网站建设,新征程启航

为企业提供网站建设、域名注册、服务器等服务

统计经典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

站在用户的角度思考问题,与客户深入沟通,找到甘泉网站设计与甘泉网站推广的解决方案,凭借多年的经验,让设计与互联网技术结合,创造个性化、用户体验好的作品,建站类型包括:成都网站建设、网站建设、企业官网、英文网站、手机端网站、网站推广、国际域名空间、虚拟主机、企业邮箱。业务覆盖甘泉地区。


分享题目:统计经典SQL(构建日期补0)
分享网址:http://dzwzjz.com/article/peohdg.html
在线咨询
服务热线
服务热线:028-86922220
TOP