如何有效率地填滿包含100年日期資料的日曆表

問題：

您需要有效率地填入一個(gè)跨越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)))    -- 復(fù)活節(jié)
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+39) -- 耶穌升天節(jié)
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+49) -- 五旬節(jié)
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+50) -- 圣靈降臨節(jié)
    OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      -- 勞動節(jié)
    OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     -- 憲法日
    OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    -- 圣誕節(jié)
    OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    -- 節(jié)禮日
    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 '復(fù)活節(jié)'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+39) THEN '耶穌升天節(jié)'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+49) THEN '五旬節(jié)'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+50) THEN '圣靈降臨節(jié)'
    WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      THEN '勞動節(jié)'
    WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     THEN '憲法日'
    WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    THEN '圣誕節(jié)'
    WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    THEN '節(jié)禮日'
END Description

FROM DatesAndThursdayInWeek d
-- 此連接用于將周數(shù)添加到結(jié)果集中
     inner join Weeks w
     on d.Thursday = w.Thursday

OPTION(MAXRECURSION 0)
GO

以上是如何使用 100 年的日期資料高效填入日曆表？的詳細(xì)內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！