【数据库】Mysql、Oracle的年、月、日sql脚本

汝之观览，吾之幸也！本文主要讲解Mysql、Oracle的年、月、日sql脚本

一、Mysql

1、当月数据

select a1.dayDate,convert(SUBSTRING(a1.dayDate,12,13),DECIMAL(10,0)) dayHour from(
select
DATE_FORMAT(DATE_ADD(curdate(),interval -day(curdate())+ac day), '%Y-%m-%d') as dayDate
FROM (
SELECT @ai:=@ai+1 as ac from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) ac1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) ac2,
(SELECT @ai:=-1) xc0
) ad)a1
where DATE_FORMAT(a1.dayDate,'%Y-%m-%d')
between DATE_FORMAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),'%Y-%m-%d') and DATE_FORMAT( last_day(curdate()),'%Y-%m-%d');

2、当天24小时数据

select DATE_FORMAT(DATE_SUB(DATE_FORMAT(now(),'%Y-%m-%d'),INTERVAL -ac+1 HOUR ) ,'%Y-%m-%d %H') dayDate
FROM (
SELECT @ai:=@ai+1 as ac from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) ac1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) ac2,
(SELECT @ai:=0) xc0
) ad;

3、当年12个月数据

select DATE_FORMAT(DATE_SUB(CONCAT(YEAR(NOW()), '-01-01'), INTERVAL -ac MONTH ) ,'%Y-%m') dayDate
FROM (
SELECT @ai:=@ai+1 as ac from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 ) ac1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) ac2,
(SELECT @ai:=-1) xc0
) ad;

二、Oracle

1、当年12个月

select '2022' dateYear,to_char(add_months(trunc(to_date('2022','yyyy'),'yyyy'),level-1),'mm') dateMonth 
from dual connect by level<=12 ;

2、前12个月

SELECT
TO_CHAR(ADD_MONTHS(TO_DATE(to_char(SYSDATE,'yyyy-mm'),'yyyy-MM'), ROWNUM - 12),'yyyy-MM')
as dateTime FROM DUAL
CONNECT BY ROWNUM <= 12;

3、前30天

SELECT to_char(day,'yyyy-mm-dd') as "days"
          from (
        SELECT to_date(to_char(trunc(sysdate, 'dd'), 'yyyy-mm-dd'), 'yyyy-mm-dd') - rownum + 1 as day
          FROM dual
                  CONNECT BY ROWNUM <= 30
        )
ORDER BY day asc;

4、近5年

SELECT to_char(SYSDATE,'YYYY') - LEVEL + 1 as dateyear
FROM dual
CONNECT BY LEVEL <= 5
ORDER BY to_char(SYSDATE,'YYYY') - LEVEL + 1 ASC;