-- 一年12月的sql
select to_char(sysdate, 'yyyy-') || lpad(level, 2, 0) datevalue
from dual
connect by level < 13;
/*
--结果
DATEVALUE
2020-01
2020-02
2020-03
2020-04
2020-05
2020-06
2020-07
2020-08
2020-09
2020-10
2020-11
2020-12
*/
with temps as(select to_char(sysdate,'yyyy') || lpad(level,2,0) mon from dual connect by level<13)
SELECT * FROM temps;
oracle获取今年在内的前几年、后几年
--前几年
-- eg 前3年
select to_char(sysdate, 'yyyy') - level + 1 years from dual connect by level <= 3 order by years;
/*
结果
YEARS
1 2018
2 2019
3 2020
*/
--后几年
-- eg 后3年
select to_char(sysdate, 'yyyy') + level - 1 years from dual connect by level <= 3 order by years;
/*
结果
YEARS
1 2020
2 2021
3 2022
*/
附 connect by level 的使用
获取连续数字示例代码:
-- 获取连续的数据(注意:level只用使用<,<=,=符号)
select level from dual connect by level <= 5 ;
/*
--结果
LEVEL
1 1
2 2
3 3
4 4
5 5
*/
获取连续的日期示例代码:
-- 获取连续的指定时间(注意:获取连续的时间需要包含当天需要再+1天)
select sysdate-level+1 days from dual connect by level <= 5 order by days;
/*
--结果
DAYS
1 2019/12/30 11:09:40
2 2019/12/31 11:09:40
3 2020/1/1 11:09:40
4 2020/1/2 11:09:40
5 2020/1/3 11:09:40
*/
统计填充示例代码:
/*
问题:查询1981年每月入职的人数,没有入职的以0补充
解决:1.创建一个连续的年份表进行关联
2.关联的条件,截取时间相等进行关联
3.注意:a.需要所有的时间,因此要让时间表主表
b.如果emp表有条件,要单独在(SELECT * FROM emp)中添加,不然会影响结果,导致时间不全
c.使用其他函数,如SUM求和可能为空用NVL函数,这里以count函数举例
*/
SELECT times.days 月份,NVL(COUNT(e.EMPNO),0) 入职人数 FROM (SELECT * FROM emp) e
RIGHT JOIN (
select TO_CHAR(ADD_MONTHS(TO_DATE('1981-12-01', 'yyyy-MM-dd'),-LEVEL+1),'yyyy-MM') days
FROM dual CONNECT BY LEVEL <= 12
) times
ON SUBSTR(TO_CHAR(e.HIREDATE,'yyyy-MM-dd hh24:mi:ss'), 0, 7) = times.days
GROUP BY times.days
ORDER BY times.days