回溯将最新Measure的Id更新至SiteTank表的技术方案咨询

1. 先给SiteTank表新增目标字段 #

首先执行ALTER语句添加存储最新Measure记录ID的字段，类型和你的Measure.Id保持一致（比如INT或UNIQUEIDENTIFIER）：

ALTER TABLE [dbo].[SiteTank]
ADD [LatestMeasureId] INT NULL; -- 类型按需调整，比如GUID就用UNIQUEIDENTIFIER

2. 优化最新Measure记录的查询逻辑 #

你原来的SELECT语句重复扫描了两次Measure表，效率偏低。可以用一次窗口函数扫描，直接筛选出每个SiteTank的最新记录：

WITH LatestMeasures AS (
    SELECT 
        M.[Id],
        M.[SiteId],
        M.[SiteTankNumber],
        -- 按站点+储罐分组，优先取最新测量日期，同日期取最晚创建的记录
        ROW_NUMBER() OVER (
            PARTITION BY M.[SiteId], M.[SiteTankNumber]
            ORDER BY M.[AsOfDate] DESC, M.[CreatedDate] DESC
        ) AS RN
    FROM [dbo].[Measure] M
)
-- 验证结果是否符合预期
SELECT SiteId, SiteTankNumber, Id AS LatestMeasureId
FROM LatestMeasures
WHERE RN = 1;

3. 批量更新SiteTank表 #

不用写存储过程循环，直接用UPDATE JOIN批量处理所有SiteTank记录，效率远高于逐行循环：

WITH LatestMeasures AS (
    SELECT 
        M.[Id],
        M.[SiteId],
        M.[SiteTankNumber],
        ROW_NUMBER() OVER (
            PARTITION BY M.[SiteId], M.[SiteTankNumber]
            ORDER BY M.[AsOfDate] DESC, M.[CreatedDate] DESC
        ) AS RN
    FROM [dbo].[Measure] M
)
UPDATE ST
SET ST.[LatestMeasureId] = LM.[Id]
FROM [dbo].[SiteTank] ST
LEFT JOIN LatestMeasures LM
    ON ST.[SiteId] = LM.[SiteId]
    AND ST.[Number] = LM.[SiteTankNumber]
WHERE LM.RN = 1; -- 仅更新有对应Measure记录的SiteTank，无记录的保持NULL

补充：未来的触发器实现示例 #

如果你要在新增Measure记录时自动更新SiteTank，这里给个高效的触发器示例（只针对涉及的站点储罐重新计算，避免全表扫描）：

CREATE TRIGGER [dbo].[trg_Measure_AfterInsert]
ON [dbo].[Measure]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    WITH LatestForInserted AS (
        SELECT 
            M.[Id],
            M.[SiteId],
            M.[SiteTankNumber],
            ROW_NUMBER() OVER (
                PARTITION BY M.[SiteId], M.[SiteTankNumber]
                ORDER BY M.[AsOfDate] DESC, M.[CreatedDate] DESC
            ) AS RN
        FROM [dbo].[Measure] M
        INNER JOIN inserted I
            ON M.[SiteId] = I.[SiteId]
            AND M.[SiteTankNumber] = I.[SiteTankNumber]
    )
    UPDATE ST
    SET ST.[LatestMeasureId] = LFI.[Id]
    FROM [dbo].[SiteTank] ST
    INNER JOIN LatestForInserted LFI
        ON ST.[SiteId] = LFI.[SiteId]
        AND ST.[Number] = LFI.[SiteTankNumber]
    WHERE LFI.RN = 1;
END

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