SQL表更新：重复行设相同GUID，唯一行分配独立GUID

要实现相同重复行的ItemPathwayGUID值一致，唯一行独立生成GUID，核心是先按重复行的标识字段分组生成一次性的GUID，再关联更新原表——直接在更新语句里调用NEWID()会导致每行生成新ID，这正是你之前遇到问题的原因。

方法1：使用CTE + 窗口函数（适用于SQL Server 2012+） #

先通过CTE为每组重复记录生成唯一GUID，再关联更新t2表：

WITH GroupedGUIDs AS (
    SELECT 
        t2.SearchComplianceBuildCodeCatID,
        -- 按t3的所有字段分组（原查询DISTINCT基于t3.*），同一组生成相同GUID
        FIRST_VALUE(NEWID()) OVER (PARTITION BY t3.SearchComplianceBuildCodeCatID, t3.字段1, t3.字段2... ORDER BY (SELECT NULL)) AS NewItemPathwayGUID
    FROM 
        SearchComplianceBuildCodeCat t1
    INNER JOIN 
        SearchProductComplianceDraft t2 ON t1.SearchComplianceBuildCodeCatID = t2.SearchComplianceBuildCodeCatID
    INNER JOIN 
        SearchComplianceBuildCodeCat t3 ON t1.SearchComplianceBuildCodeCatID_Parent = t3.SearchComplianceBuildCodeCatID
    WHERE
        t1.SearchComplianceBuildCodeCatLevel = 3
        AND t1.Visible = 1
        AND t3.Visible = 1
)
UPDATE t2
SET t2.ItemPathwayGUID = g.NewItemPathwayGUID
FROM SearchProductComplianceDraft t2
JOIN GroupedGUIDs g ON t2.SearchComplianceBuildCodeCatID = g.SearchComplianceBuildCodeCatID;

注意：PARTITION BY后要列出t3表中所有用来判断重复的字段（即原查询t3.*包含的全部列）。如果t3的主键SearchComplianceBuildCodeCatID可以唯一标识分组，仅保留该字段即可。

方法2：使用临时表存储分组GUID #

如果你的SQL版本不支持窗口函数，可先将分组后的GUID存入临时表，再关联更新：

-- 创建临时表，存储每个分组对应的唯一GUID
SELECT 
    t3.SearchComplianceBuildCodeCatID,
    NEWID() AS ItemPathwayGUID
INTO #TempGUIDs
FROM 
    SearchComplianceBuildCodeCat t1
INNER JOIN 
    SearchProductComplianceDraft t2 ON t1.SearchComplianceBuildCodeCatID = t2.SearchComplianceBuildCodeCatID
INNER JOIN 
    SearchComplianceBuildCodeCat t3 ON t1.SearchComplianceBuildCodeCatID_Parent = t3.SearchComplianceBuildCodeCatID
WHERE
    t1.SearchComplianceBuildCodeCatLevel = 3
    AND t1.Visible = 1
    AND t3.Visible = 1
GROUP BY t3.SearchComplianceBuildCodeCatID, t3.字段1, t3.字段2...; -- 列出所有分组字段

-- 关联更新原表
UPDATE t2
SET t2.ItemPathwayGUID = temp.ItemPathwayGUID
FROM SearchProductComplianceDraft t2
JOIN SearchComplianceBuildCodeCat t1 ON t2.SearchComplianceBuildCodeCatID = t1.SearchComplianceBuildCodeCatID
JOIN #TempGUIDs temp ON t1.SearchComplianceBuildCodeCatID_Parent = temp.SearchComplianceBuildCodeCatID;

-- 清理临时表
DROP TABLE #TempGUIDs;

验证结果 #

更新完成后，重新执行你的原查询：

SELECT DISTINCT
    t3.*, t2.ItemPathwayGUID
FROM 
    SearchComplianceBuildCodeCat t1
INNER JOIN 
    SearchProductComplianceDraft t2 ON t1.SearchComplianceBuildCodeCatID = t2.SearchComplianceBuildCodeCatID
INNER JOIN 
    SearchComplianceBuildCodeCat t3 ON t1.SearchComplianceBuildCodeCatID_Parent = t3.SearchComplianceBuildCodeCatID
WHERE
    t1.SearchComplianceBuildCodeCatLevel = 3
    AND t1.Visible = 1
    AND t3.Visible = 1

此时应返回29行，且相同t3.*组合的记录对应的ItemPathwayGUID完全一致。

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