如何用T-SQL创建含周编号及对应日期范围的数据表？

嘿，刚好做过类似的需求，给你分享两种靠谱的T-SQL实现方式，完美满足你要的每条记录对应周编号+日期范围标签的需求：

方法1：创建持久化的周标签数据表（适合长期使用） #

如果需要把这些周标签数据持久存储起来，方便后续查询或关联其他表，先创建基础表结构，再用递归CTE自动生成数据：

首先创建表：

CREATE TABLE WeeklyDateLabels (
    WeekID INT PRIMARY KEY IDENTITY(1,1), -- 自增主键，方便关联其他表
    WeekLabel VARCHAR(50) NOT NULL, -- 你要的'week #1: 1/1/18 - 1/7/18'格式标签
    StartDate DATE NOT NULL, -- 单独存储起始日期，方便后续日期筛选
    EndDate DATE NOT NULL -- 单独存储结束日期
);

然后生成指定时间范围内的周数据并插入表中：

-- 定义你要生成的时间范围
DECLARE @StartDate DATE = '2018-01-01';
DECLARE @EndDate DATE = '2018-12-31'; -- 可根据需求修改结束日期

WITH WeeklyCTE AS (
    -- 初始行：生成第一周的基础信息
    SELECT
        1 AS WeekNumber,
        @StartDate AS CurrentStartDate,
        DATEADD(DAY, 6, @StartDate) AS CurrentEndDate
    UNION ALL
    -- 递归生成后续每一周的数据
    SELECT
        WeekNumber + 1,
        DATEADD(DAY, 7, CurrentStartDate), -- 下一周起始日=当前起始日+7天
        DATEADD(DAY, 6, DATEADD(DAY, 7, CurrentStartDate)) -- 下一周结束日=下一周起始日+6天
    FROM WeeklyCTE
    WHERE DATEADD(DAY, 7, CurrentStartDate) <= @EndDate -- 递归终止条件：下一周起始日不超过设定的结束日期
)
INSERT INTO WeeklyDateLabels (WeekLabel, StartDate, EndDate)
SELECT
    -- 拼接成你需要的标签格式
    CONCAT('week #', WeekNumber, ': ', 
           CAST(MONTH(CurrentStartDate) AS VARCHAR), '/', CAST(DAY(CurrentStartDate) AS VARCHAR), '/', RIGHT(YEAR(CurrentStartDate), 2),
           ' - ',
           CAST(MONTH(CurrentEndDate) AS VARCHAR), '/', CAST(DAY(CurrentEndDate) AS VARCHAR), '/', RIGHT(YEAR(CurrentEndDate), 2)) AS WeekLabel,
    CurrentStartDate,
    CurrentEndDate
FROM WeeklyCTE
OPTION (MAXRECURSION 0); -- 解除递归次数限制（默认最多100次，生成超过52周的话必须加这个）

方法2：动态生成周标签（无需存储表，临时查询用） #

如果只是临时需要生成这些标签，不需要持久化存储，直接用CTE查询输出即可：

DECLARE @StartDate DATE = '2018-01-01';
DECLARE @EndDate DATE = '2018-12-31';

WITH WeeklyCTE AS (
    SELECT
        1 AS WeekNumber,
        @StartDate AS CurrentStartDate,
        DATEADD(DAY, 6, @StartDate) AS CurrentEndDate
    UNION ALL
    SELECT
        WeekNumber + 1,
        DATEADD(DAY, 7, CurrentStartDate),
        DATEADD(DAY, 6, DATEADD(DAY, 7, CurrentStartDate))
    FROM WeeklyCTE
    WHERE DATEADD(DAY, 7, CurrentStartDate) <= @EndDate
)
SELECT
    CONCAT('week #', WeekNumber, ': ', 
           CAST(MONTH(CurrentStartDate) AS VARCHAR), '/', CAST(DAY(CurrentStartDate) AS VARCHAR), '/', RIGHT(YEAR(CurrentStartDate), 2),
           ' - ',
           CAST(MONTH(CurrentEndDate) AS VARCHAR), '/', CAST(DAY(CurrentEndDate) AS VARCHAR), '/', RIGHT(YEAR(CurrentEndDate), 2)) AS WeekLabel,
    CurrentStartDate,
    CurrentEndDate
FROM WeeklyCTE
OPTION (MAXRECURSION 0);

小提示 #

1. 日期格式化兼容：上面用的拼接方式兼容所有SQL Server版本。如果你的SQL Server是2012及以上，也可以用更简洁的FORMAT函数，比如FORMAT(CurrentStartDate, 'M/d/yy')直接得到1/1/18格式。
2. 调整周起始日：如果业务中周不是从周日开始（比如从周一），可以把初始的@StartDate调整为对应周的起始日，比如用DATEADD(DAY, DATEDIFF(DAY, 1, @StartDate)/7*7, 1)来获取离@StartDate最近的周一作为第一周起始日。

内容的提问来源于stack exchange，提问作者cmpmd2