Wie füllt man eine Kalendertabelle effizient mit Datumsdaten aus 100 Jahren?

So füllen Sie effizient eine Kalendertabelle mit 100-Jahres-Datumsdaten aus

Frage:

Sie müssen einen Kalender, der 100 Jahre umfasst, effizient mit Datums-, Jahr-, Monats-, Wochen- und Datumsinformationen füllen.

Antwort:

Erwägen Sie die Verwendung eines speziellen Skripts, um einen umfassenderen Kalender zu erstellen, wie beispielsweise das von Microsoft bereitgestellte:

<code class="language-sql">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</code>

Das obige ist der detaillierte Inhalt vonWie füllt man eine Kalendertabelle effizient mit Datumsdaten aus 100 Jahren?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!