Comment remplir efficacement une table de calendrier avec 100 ans de données de date ?

Comment remplir efficacement un tableau de calendrier contenant 100 ans de données de date

Question :

Vous devez remplir efficacement un calendrier s'étendant sur 100 ans avec des informations sur la date, l'année, le mois, la semaine et la date.

Réponse :

Pensez à utiliser un script spécialisé pour créer un calendrier plus complet, comme celui fourni par 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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!