编写SQL脚本更新同PrimaryFamilyID成员的dbo.AttributeValue属性值

业务背景 #

我们需要实现同家庭下公共属性修改后全员同步的能力：所有在dbo.Person表中PrimaryFamilyID取值相同的家庭成员，其dbo.AttributeValue表中对应公共属性的取值，需同步为该家庭内该属性最新ModifiedDateTime对应的数值，支持修改后自动同步。 #

涉及表核心字段说明 #

• dbo.Person表：
  • ID：用户唯一标识，与AttributeValue表的EntityID关联
  • PrimaryFamilyID：家庭唯一标识，同值用户为同一家庭成员
• dbo.AttributeValue表：
  • AttributeID：属性唯一标识，公共属性可预先配置ID范围，本方案以样例ID 5856为例
  • EntityID：关联的用户ID
  • Value：属性存储值
  • CreatedDateTime：属性创建时间
  • ModifiedDateTime：属性最后修改时间

方案1：存量数据一次性同步脚本 #

适用于首次上线时全量同步历史公共属性数据：

-- 1. 配置需要同步的公共属性ID列表，可根据业务需求新增多个ID
DECLARE @PublicAttributeIDs TABLE (AttributeID INT);
INSERT INTO @PublicAttributeIDs VALUES (5856);

-- 2. 提取每个家庭每个公共属性的最新值
WITH FamilyAttributeLatest AS (
    SELECT 
        p.PrimaryFamilyID,
        av.AttributeID,
        av.Value,
        av.CreatedDateTime,
        av.ModifiedDateTime,
        ROW_NUMBER() OVER (PARTITION BY p.PrimaryFamilyID, av.AttributeID ORDER BY av.ModifiedDateTime DESC) AS RN
    FROM dbo.AttributeValue av
    JOIN dbo.Person p ON av.EntityID = p.ID
    WHERE av.AttributeID IN (SELECT AttributeID FROM @PublicAttributeIDs)
),
FamilyLatestValue AS (
    SELECT * FROM FamilyAttributeLatest WHERE RN = 1
),
-- 关联所有家庭成员的属性记录，过滤出需要更新的条目
FamilyNeedUpdate AS (
    SELECT 
        av.AttributeID,
        av.EntityID,
        flv.Value AS LatestValue,
        flv.CreatedDateTime AS LatestCreatedTime,
        flv.ModifiedDateTime AS LatestModifiedTime
    FROM dbo.AttributeValue av
    JOIN dbo.Person p ON av.EntityID = p.ID
    JOIN FamilyLatestValue flv ON p.PrimaryFamilyID = flv.PrimaryFamilyID AND av.AttributeID = flv.AttributeID
    WHERE av.AttributeID IN (SELECT AttributeID FROM @PublicAttributeIDs)
    AND (av.Value <> flv.Value OR av.ModifiedDateTime <> flv.ModifiedDateTime)
)
-- 执行同步更新
UPDATE FamilyNeedUpdate
SET 
    Value = LatestValue,
    CreatedDateTime = LatestCreatedTime,
    ModifiedDateTime = LatestModifiedTime;

注意：运行前可先将最后的UPDATE语句替换为SELECT * FROM FamilyNeedUpdate，确认待更新的记录符合预期后再执行更新操作，避免数据错误。

方案2：触发式自动同步方案 #

适用于上线后用户修改属性时实时自动同步，无需人工干预，在dbo.AttributeValue表上创建INSERT/UPDATE触发器即可：

CREATE TRIGGER trg_AttributeValue_SyncFamilyPublicAttribute
ON dbo.AttributeValue
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    -- 公共属性配置，也可以关联业务侧专门的公共属性配置表读取
    DECLARE @PublicAttributeIDs TABLE (AttributeID INT);
    INSERT INTO @PublicAttributeIDs VALUES (5856);

    -- 仅修改公共属性时才触发同步，减少无效计算
    IF EXISTS (SELECT 1 FROM inserted WHERE AttributeID IN (SELECT AttributeID FROM @PublicAttributeIDs))
    BEGIN
        WITH ChangedLatestValue AS (
            SELECT 
                p.PrimaryFamilyID,
                i.AttributeID,
                i.Value,
                i.CreatedDateTime,
                i.ModifiedDateTime,
                ROW_NUMBER() OVER (PARTITION BY p.PrimaryFamilyID, i.AttributeID ORDER BY i.ModifiedDateTime DESC) AS RN
            FROM inserted i
            JOIN dbo.Person p ON i.EntityID = p.ID
            WHERE i.AttributeID IN (SELECT AttributeID FROM @PublicAttributeIDs)
        ),
        FamilyLatest AS (
            SELECT * FROM ChangedLatestValue WHERE RN = 1
        )
        UPDATE av
        SET 
            av.Value = fl.Value,
            av.CreatedDateTime = fl.CreatedDateTime,
            av.ModifiedDateTime = fl.ModifiedDateTime
        FROM dbo.AttributeValue av
        JOIN dbo.Person p ON av.EntityID = p.ID
        JOIN FamilyLatest fl ON p.PrimaryFamilyID = fl.PrimaryFamilyID AND av.AttributeID = fl.AttributeID
        WHERE av.AttributeID IN (SELECT AttributeID FROM @PublicAttributeIDs)
        AND (av.Value <> fl.Value OR av.ModifiedDateTime <> fl.ModifiedDateTime);
    END
END
GO

样例验证结果 #

按照上述方案执行后，样例中PrimaryFamilyID=187的3名用户（ID为733、709、137），其AttributeID=5856的属性值均会同步为最新的True，CreatedDateTime、ModifiedDateTime也和最新记录保持一致，符合预期。

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