如何无游标更新分组内日期小于最大日期减指定天数的记录

需求：更新表中记录，当某条记录的Header_BusDate小于其所属Header_SiteKey分组下的最大Header_BusDate减去指定天数（示例为2天）时，将该记录的Header_Ready字段设为1，要求不使用游标。

示例表与数据 #

CREATE TABLE #Headers
(
    Header_Key      decimal(15,0)   NOT NULL,
    Header_SiteKey  decimal(15,0)   NOT NULL,
    Header_BusDate  smalldatetime   NOT NULL,
    Header_Ready    bit             NOT NULL default(0)
)

INSERT INTO #Headers
VALUES
(610, 4, '2023-04-16', 0),
(609, 4, '2023-04-15', 0),
(608, 4, '2023-04-14', 0),
(607, 4, '2023-04-13', 0),
(606, 4, '2023-04-12', 0),
(605, 4, '2023-04-11', 0),
(604, 4, '2023-04-10', 0),
(617, 6, '2032-12-31', 0),
(616, 6, '2023-04-15', 0),
(615, 6, '2023-04-14', 0),
(614, 6, '2023-04-13', 0),
(613, 6, '2023-04-12', 0),
(612, 6, '2023-04-11', 0),
(611, 6, '2023-04-10', 0)

原尝试的错误语句 #

以下语句无法正常工作，原因是子查询中未正确关联外部表字段，导致分组逻辑失效：

UPDATE #Headers
   SET Header_Ready = 1
 WHERE Header_BusDate < 
       (SELECT MAX(Header_BusDate) - 2 day
          FROM #Headers hdrs 
         WHERE hdrs.Header_SiteKey = Header_SiteKey)

期望结果 #

Header_Key  Header_SiteKey  Header_BusDate      Header_Ready
610         4               2023-04-16 00:00:00            0
609         4               2023-04-15 00:00:00            0
608         4               2023-04-14 00:00:00            0
607         4               2023-04-13 00:00:00            1
606         4               2023-04-12 00:00:00            1
605         4               2023-04-11 00:00:00            1
604         4               2023-04-10 00:00:00            1
617         6               2032-12-31 00:00:00            0
616         6               2023-04-15 00:00:00            1
615         6               2023-04-14 00:00:00            1
614         6               2023-04-13 00:00:00            1
613         6               2023-04-12 00:00:00            1
612         6               2023-04-11 00:00:00            1
611         6               2023-04-10 00:00:00            1

可行解决方案 #

方案1：修正原有的关联子查询方式 #

给外部表添加别名，明确子查询与外部表的关联关系：

UPDATE h
SET Header_Ready = 1
FROM #Headers h
WHERE h.Header_BusDate < 
      (SELECT MAX(hdrs.Header_BusDate) - 2 
         FROM #Headers hdrs 
        WHERE hdrs.Header_SiteKey = h.Header_SiteKey)

方案2：使用JOIN + 分组查询（性能更优，适合大数据量） #

先通过分组查询计算每个Header_SiteKey的最大日期，再关联原表进行更新：

-- 方法2.1：使用CTE
WITH SiteMaxDates AS (
    SELECT 
        Header_SiteKey,
        MAX(Header_BusDate) AS Max_BusDate
    FROM #Headers
    GROUP BY Header_SiteKey
)
UPDATE h
SET h.Header_Ready = 1
FROM #Headers h
JOIN SiteMaxDates smd ON h.Header_SiteKey = smd.Header_SiteKey
WHERE h.Header_BusDate < DATEADD(day, -2, smd.Max_BusDate);

-- 方法2.2：直接使用子查询JOIN
UPDATE h
SET Header_Ready = 1
FROM #Headers h
INNER JOIN (
    SELECT 
        Header_SiteKey,
        MAX(Header_BusDate) AS Max_BusDate
    FROM #Headers
    GROUP BY Header_SiteKey
) smd ON h.Header_SiteKey = smd.Header_SiteKey
WHERE h.Header_BusDate < DATEADD(day, -2, smd.Max_BusDate);

说明 #

• 方案1通过明确字段关联修正了原语句的逻辑问题，写法简洁；
• 方案2先预计算分组最大日期，避免了子查询的重复执行，在数据量较大时性能更优；
• DATEADD(day, -2, smd.Max_BusDate)等价于MAX(Header_BusDate) - 2，写法更通用，适配不同SQL Server版本。

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