统计经典SQL(构建日期补0)-白红宇

统计经典SQL(构建日期补0)

阅读量：7179 次

发布时间：2019-06-29

本文共 922 字，大约阅读时间需要 3 分钟。

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

转载于:https://blog.51cto.com/hebinteng/2362483

你可能感兴趣的文章

java B2B2C Springcloud电子商城系统-Spring Cloud常见问题与总结(四)

查看>>

2017双11技术揭秘—阿里巴巴数据库技术架构演进

查看>>

聊聊字典编码

查看>>

独家 | 史上最权威的BI 趋势分析及产品对比

查看>>

观点 | 云原生时代来袭下一代云数据库技术将走向何方？

查看>>

互联网分布式微服务云平台规划分析--SSO单点登录系统

查看>>

经典以太坊是什么

查看>>

SpringCloud之高可用的分布式配置中心(Spring Cloud Config)（七）

查看>>

h5实现微信扫码

查看>>

curl: (6) Couldn't resolve host 'www.ttlsa.com'

文件名过长/文件夹路径过长无法删除文件夹之解决办法