创建包含过去6个月的周编号及其各自的月份和年份的表。SQL Server

CREATE TABLE WeekTable (
    WeekNumber INT,
    MonthNumber INT,
    YearNumber INT
);

DECLARE @StartDate DATE = DATEADD(MONTH, -6, GETDATE());
DECLARE @EndDate DATE = GETDATE();

WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO WeekTable (WeekNumber, MonthNumber, YearNumber)
    SELECT DATEPART(WEEK, DateColumn) AS WeekNumber,
           DATEPART(MONTH, DateColumn) AS MonthNumber,
           DATEPART(YEAR, DateColumn) AS YearNumber
    FROM YourTable
    WHERE DateColumn >= @StartDate AND DateColumn <= DATEADD(DAY, 6, @StartDate);

    SET @StartDate = DATEADD(DAY, 7, @StartDate);
END