备案图标怎么放在网站中,建设项目环境影响评价登记表网站,网站搜索引擎优化怎么做,爬虫 wordpress几种生成日期表的方法 如何用SQL语句生成日期表呢#xff1f; 如何用MySQL语句生成日期表呢#xff1f; 如何用DAX语句生成日期表呢#xff1f;
1. MySQL生成日期表
1.1 日期格式#xff1a;yyyy-MM-dd 字符型 2024-01-02
-- 生成日期表
WITH RECURSIVE temp_dateTable …几种生成日期表的方法 如何用SQL语句生成日期表呢 如何用MySQL语句生成日期表呢 如何用DAX语句生成日期表呢
1. MySQL生成日期表
1.1 日期格式yyyy-MM-dd 字符型 2024-01-02
-- 生成日期表
WITH RECURSIVE temp_dateTable AS (SELECT 2023-01-01 AS datekeyUNION ALLSELECT DATE_ADD(datekey, INTERVAL 1 DAY)FROM temp_dateTableWHERE datekey 2023-12-31
)
SELECT datekey FROM temp_dateTable
;
/*
datekey
2023-01-01
2023-01-02
2023-01-03
2023-01-04
*/1.2 日期格式yyyyMMdd 数字型 20240102
-- 生成日期表
WITH RECURSIVE temp_dateTable AS (SELECT 20240101 AS datekeyUNION ALLSELECT date_format(date_add(CONVERT(datekey, CHAR),interval 1 day), %Y%m%d)0FROM temp_dateTableWHERE datekey 20240117
)
selectdt.datekey
from temp_dateTable dt
;
/*
datekey
20240101
20240102
20240103
20240104
*/1.3 MySQL生成的日期表插入到实体表临时表
-- 删除表
DROP TABLE IF EXISTS create_dateTable;-- 创建表
CREATE TABLE create_dateTable (datekey DATE PRIMARY KEY
);-- 将查找出来的临时表插入到新创建的实体表
INSERT INTO create_dateTable
WITH RECURSIVE temp_dateTable AS (SELECT 2023-01-01 AS datekeyUNION ALLSELECT DATE_ADD(datekey, INTERVAL 1 DAY)FROM temp_dateTableWHERE datekey 2023-12-31
)
SELECT datekey FROM temp_dateTable;
-- 查找日期
SELECT * FROM create_dateTable;2. SQL生成日期表
2.1 日期格式yyyy-MM-dd 字符型 2024-01-02
DECLARE BeginDate DATE, EndDate DATE;
SET BeginDate 2023-09-01
SET EndDate 2023-12-01
;
WITH cteDate AS (SELECT BeginDate AS CalendarDateUNION ALLSELECT DATEADD(DAY,1,CalendarDate)FROM cteDateWHERE CalendarDate EndDate
)
SELECTCalendarDate DateKey,(DATEPART(YEAR,CalendarDate) * 10000) (DATEPART(MONTH,CalendarDate) * 100) DATEPART(DAY,CalendarDate) CalendarDate,DATEPART(YEAR,CalendarDate) AS Year,DATEPART(MONTH,CalendarDate) AS MonthNumber,DATENAME(MONTH,CalendarDate) AS Month,DATENAME(QUARTER,CalendarDate) AS Quarter, format(CalendarDate ,yyyyMM) YeamMonth
FROM cteDate
OPTION (MAXRECURSION 0)2.2 日期格式yyyyMMdd 数字型 20240102
DECLARE BeginDate INT, EndDate INT;
SET BeginDate 20240101;
SET EndDate 20240117;
WITH cteDate AS (SELECT BeginDate AS CalendarDateUNION ALLSELECT format(dateadd(day, 1, cast(CalendarDate as varchar(8))), yyyyMMdd)0FROM cteDateWHERE CalendarDate EndDate
)
selectCalendarDate
from cteDate
/*
CalendarDate
20240101
20240102
20240103
20240104
*/2.3 SQL生成的日期表插入到实体表临时表
DECLARE BeginDate DATE, EndDate DATE;
SET BeginDate 2023-09-01
SET EndDate 2023-12-01
;
WITH cteDate AS (SELECT BeginDate AS CalendarDateUNION ALLSELECT DATEADD(DAY,1,CalendarDate)FROM cteDateWHERE CalendarDate EndDate
)
SELECTCalendarDate DateKey,(DATEPART(YEAR,CalendarDate) * 10000) (DATEPART(MONTH,CalendarDate) * 100) DATEPART(DAY,CalendarDate) CalendarDate,DATEPART(YEAR,CalendarDate) AS Year,DATEPART(MONTH,CalendarDate) AS MonthNumber,DATENAME(MONTH,CalendarDate) AS Month,DATENAME(QUARTER,CalendarDate) AS Quarter, format(CalendarDate ,yyyyMM) YeamMonthINTO #temp_dateTable
FROM cteDate
OPTION (MAXRECURSION 0);
SELECT * FROM #temp_dateTable3. DAX日期表生成 自动日期表
EVALUATE
ADDCOLUMNS (CALENDARAUTO(),Year, YEAR ( [Date] ),Quarter No, QUARTER ( [Date] ),Quarter, Q QUARTER ( [Date] ),Month No, MONTH ( [Date] ),Month, FORMAT ( [Date], MM ),Day, DAY ( [Date] )
)3.2 DAX日期表生成 指定日期表
EVALUATE
ADDCOLUMNS (CALENDAR ( DATE ( 2023, 11, 01 ), DATE ( 2023, 12, 31 ) ),Year, YEAR ( [Date] ),Quarter No, QUARTER ( [Date] ),Quarter, Q QUARTER ( [Date] ),Month No, MONTH ( [Date] ),Month, FORMAT ( [Date], MM ),Day, DAY ( [Date] )
)
