100년 동안의 날짜 데이터로 달력 테이블을 효율적으로 채우는 방법은 무엇입니까?

100년간의 날짜 데이터가 포함된 달력 테이블을 효율적으로 채우는 방법

질문:

100년에 걸친 달력을 날짜, 연도, 월, 주 및 날짜 정보로 효율적으로 채워야 합니다.

정답:

Microsoft에서 제공하는 것과 같은 보다 포괄적인 달력을 만들려면 특수 스크립트를 사용하는 것이 좋습니다.

SET DATEFIRST 1;
WITH Dates(Date)
AS
(
SELECT cast('1999' AS DateTime) -- SQL Server支持ISO 8601格式
UNION ALL
SELECT (Date + 1) AS Date
FROM Dates
WHERE
Date < cast('2099' AS DateTime) -1
),
DatesAndThursdayInWeek(Date, Thursday)
AS
(
SELECT
Date,
CASE DATEPART(weekday,Date)
WHEN 1 THEN Date + 3
WHEN 2 THEN Date + 2
WHEN 3 THEN Date + 1
WHEN 4 THEN Date
WHEN 5 THEN Date - 1
WHEN 6 THEN Date - 2
WHEN 7 THEN Date - 3
END AS Thursday
FROM Dates
),
Weeks(Week, Thursday)
AS
(
SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week, Thursday
FROM DatesAndThursdayInWeek
WHERE DATEPART(weekday,Date) = 4
)
INSERT INTO Auxiliary.Calendar
SELECT
d.Date,
YEAR(d.Date) AS Year,
DATEPART(Quarter, d.Date) AS Quarter,
MONTH(d.Date) AS Month,
w.Week,
DAY(d.Date) AS Day,
DATEPART(DayOfYear, d.Date) AS DayOfYear,
DATEPART(Weekday, d.Date) AS Weekday,

YEAR(d.Date) AS Fiscal_Year,
DATEPART(Quarter, d.Date) AS Fiscal_Quarter,
MONTH(d.Date) AS Fiscal_Month,

CASE
    WHEN (DATEPART(DayOfYear, d.Date) = 1)          -- 元旦
    OR (d.Date = Auxiliary.Computus(YEAR(Date))-7)  -- 棕枝主日
    OR (d.Date = Auxiliary.Computus(YEAR(Date))-3)  -- 圣周四
    OR (d.Date = Auxiliary.Computus(YEAR(Date))-2)  -- 受难日
    OR (d.Date = Auxiliary.Computus(YEAR(Date)))    -- 复活节
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+39) -- 耶稣升天节
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+49) -- 五旬节
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+50) -- 圣灵降临节
    OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      -- 劳动节
    OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     -- 宪法日
    OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    -- 圣诞节
    OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    -- 节礼日
    THEN 'HOLIDAY'
    WHEN DATEPART(Weekday, d.Date) = 6 THEN 'SATURDAY'
    WHEN DATEPART(Weekday, d.Date) = 7 THEN 'SUNDAY'
    ELSE 'BANKDAY'
END KindOfDay,
CASE
    WHEN (DATEPART(DayOfYear, d.Date) = 1)            THEN '元旦'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-7)  THEN '棕枝主日'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-3)  THEN '圣周四'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-2)  THEN '受难日'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date)))    THEN '复活节'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+39) THEN '耶稣升天节'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+49) THEN '五旬节'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+50) THEN '圣灵降临节'
    WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      THEN '劳动节'
    WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     THEN '宪法日'
    WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    THEN '圣诞节'
    WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    THEN '节礼日'
END Description

FROM DatesAndThursdayInWeek d
-- 此连接用于将周数添加到结果集中
     inner join Weeks w
     on d.Thursday = w.Thursday

OPTION(MAXRECURSION 0)
GO

위 내용은 100년 동안의 날짜 데이터로 달력 테이블을 효율적으로 채우는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!