MS SQL：如何结合GROUP BY实现SubDuplicateNo字段自增？

我看了你的代码和问题，核心问题出在你用来生成SubDubletNo的窗口函数逻辑不对，以及后续的GROUP BY操作打乱了编号的生成。让我一步步拆解问题并给出修复方案：

原代码的问题分析 #

1. 你在内层用了DENSE_RANK() OVER (PARTITION BY 所有重复判断字段 ORDER BY 同样的字段)：因为PARTITION BY已经把相同的记录分到一组了，ORDER BY的字段又完全一样，所以每个组里的所有行的rank值都是1，这就是为什么SubDubletNo始终为1的原因。
2. 外层的GROUP BY带上了subdubletno，这会把原本的重复记录拆分成更小的组，进一步破坏了编号逻辑，后续修改后全变1也和这个逻辑有关。

修复后的SQL代码 #

我们需要调整窗口函数的用法，先标记重复组，再分别生成组编号和组内序号：

WITH cte AS (
    SELECT 
        V1.Vennenr,
        V1.Upper_Fornavn_FirstPart,
        V1.Upper_Efternavn,
        MA1.Upper_Adresse,
        V1.Telefon,
        MA1.Upper_postnr,
        -- 生成重复组的编号：每个唯一的重复组对应一个DubletNo
        DENSE_RANK() OVER (ORDER BY V1.Upper_Fornavn_FirstPart, V1.Upper_Efternavn, MA1.Upper_Adresse, V1.Telefon, MA1.Upper_postnr) AS DubletNo,
        -- 生成组内的自增序号：每个组内从1开始计数
        ROW_NUMBER() OVER (PARTITION BY V1.Upper_Fornavn_FirstPart, V1.Upper_Efternavn, MA1.Upper_Adresse, V1.Telefon, MA1.Upper_postnr ORDER BY V1.Vennenr) AS SubDubletNo,
        -- 标记当前组的总记录数，用来筛选重复组
        COUNT(*) OVER (PARTITION BY V1.Upper_Fornavn_FirstPart, V1.Upper_Efternavn, MA1.Upper_Adresse, V1.Telefon, MA1.Upper_postnr) AS GroupCount
    FROM Medlemsdata V1
    LEFT JOIN MedlemsAdresse MA1 ON V1.FK_AdrID = MA1.AdrID
    LEFT JOIN Postnumre ON Postnumre.Postnummer = MA1.Postnr
    WHERE 
        V1.vennenr > 0 
        AND V1.Upper_Fornavn_FirstPart <> '' 
        AND V1.Upper_Efternavn <> '' 
        AND MA1.Upper_Adresse <> '' 
        AND V1.Telefon <> '' 
        AND MA1.Upper_postnr <> ''
)
-- 只保留重复组的记录（组内记录数>1）
SELECT 
    Vennenr,
    Upper_Fornavn_FirstPart,
    Upper_Efternavn,
    Upper_Adresse,
    Telefon,
    Upper_postnr,
    DubletNo,
    SubDubletNo
FROM cte
WHERE GroupCount > 1
ORDER BY DubletNo, SubDubletNo;

关键逻辑说明 #

• DubletNo：用DENSE_RANK()按重复字段排序，这样每个唯一的重复组合会得到一个连续的编号（比如ANNE JENSEN的组是1，TOM HANSEN的组是2）。
• SubDubletNo：用ROW_NUMBER()按重复字段分组，然后按Vennenr（或者你需要的其他唯一字段）排序，这样每个组内的记录会从1开始自增。
• GroupCount：用COUNT() OVER (PARTITION BY ...)来统计每个组的总记录数，最后筛选出记录数>1的组，也就是真正的重复数据。

这样执行后，就能得到你预期的结果：每个重复组有唯一的DubletNo，组内的SubDubletNo从1开始依次递增。

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