【数据库】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+1as ac from(SELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6) ac1,(SELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6) ac2,(SELECT@ai:=-1) xc0
) ad)a1
where DATE_FORMAT(a1.dayDate,'%Y-%m-%d')between DATE_FORMAT(DATE_ADD(curdate(),interval-day(curdate())+1day),'%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+1HOUR),'%Y-%m-%d %H') dayDate
FROM(SELECT@ai:=@ai+1as ac from(SELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4) ac1,(SELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6) 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+1as ac from(SELECT1UNIONSELECT2UNIONSELECT3) ac1,(SELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4) 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 connectbylevel<=12;

2、前12个月

SELECT
TO_CHAR(ADD_MONTHS(TO_DATE(to_char(SYSDATE,'yyyy-mm'),'yyyy-MM'), ROWNUM -12),'yyyy-MM')asdateTimeFROM DUAL
CONNECTBY 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 +1asdayFROM dual
                  CONNECTBY ROWNUM <=30)ORDERBYdayasc;

4、近5年

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