ORACLE 查询指定时间之内的日、月及其他汇总

oracle查询一年内所有日期：
SELECT
TO_CHAR( SYSDATE - interval ‘1’ year + ROWNUM, ‘YYYY-MM-DD’ ) checkDate
FROM
DUAL CONNECT BY ROWNUM <= SYSDATE - (SYSDATE - interval ‘1’ year)
ORDER BY checkDate DESC

oracle查询本月所有日期：
select TRUNC(sysdate, ‘MM’) + ROWNUM - 1 as monthDate
from DUAL connect by ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(sysdate), ‘dd’))

mysql查询一年内所有日期：
select a.checkDate
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as checkDate
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.checkDate between curdate() - INTERVAL 1 YEAR and curdate()

————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/qq_40295426/article/details/111987240

 

– 连续日期的记录

SELECT (TO_DATE('20211001', 'YYYYMMDD' )+ ROWNUM - 1)
FROM DUAL
CONNECT BY ROWNUM <= TO_DATE('20211007', 'yyyymmdd') - TO_DATE('20211001', 'yyyymmdd') + 1;

– 连续月份的记录

SELECT TO_CHAR( ADD_MONTHS( (TO_DATE('202101', 'YYYYMM' )+ ROWNUM - 1) , ROWNUM -1) , 'YYYYMM')
FROM DUAL
CONNECT BY ROWNUM <= MONTHs_BETWEEN(TO_DATE('202112', 'yyyymm') , TO_DATE('202101', 'yyyymm')) + 1;

-- 逐月累计数量
select 日期,sum(数量) over(order by 日期) from test order by 日期;
-- 按照单位逐月累计数量
select 单位 , 日期 ,sum(数量) over(partition by 单位 order by 单位 , 日期) from test;
-- 按照单位逐月累计数量示例1
select 省份, 日期, sum(电量) over(partition by 省份 order by 省份 , 日期) from 表名 order by 日期

 

-- 合并单位逐月累计数量示例1
select 日期, sum(电量) over(order by 日期)
from (select 日期,
sum(电量) as 电量
from 表名
group by 日期
)
order by 日期;
————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/chensq_yinhai/article/details/124270646