SQL中使用NEXT VALUE FOR生成ID时，如何结合DISTINCT去重？

你遇到的问题是用NEXT VALUE FOR生成唯一ID时，结果出现重复行；直接用SELECT DISTINCT会触发语法错误，而用GROUP BY又因列数过多太繁琐，尝试子查询去重后仍有重复。

核心思路 #

问题根源在于NEXT VALUE FOR是行级执行的——每一行数据都会触发一次序列值生成，哪怕其他列完全相同，生成的ID也会不一样，这就导致DISTINCT无法识别重复行；同时SQL语法禁止在包含DISTINCT/UNION等操作的语句中直接使用该函数。

正确的解决逻辑是：先得到去重后的唯一数据集，再给这个数据集的每一行分配唯一ID。

方法1：用CTE先去重，再生成ID #

先通过CTE（公共表表达式）筛选出所有唯一的非ID列数据，再在外层查询中为这些唯一行生成ID：

WITH UniqueRows AS (
    SELECT DISTINCT
        CONVERT(VARCHAR(255), progdetail.programmecode) AS PROGRAMME
        , CONVERT(VARCHAR(50), CONCAT(modudet.code, 'CM-2024')) AS REFERENCE
        , CONVERT(VARCHAR(255), modudet.longtitle) AS NAME
        , CONVERT(VARCHAR(10), 'Module') AS TYPE
    FROM [TABLE1] progdetail 
    JOIN [TABLE2] modudet ON progdetail.child_entitycuid = modudet.module_cuid
    WHERE progdetail.programmecode IN (
        -- 子查询里的DISTINCT可省略，GROUP BY已保证唯一性
        SELECT programmecode 
        FROM TABLE2 
        GROUP BY programmecode 
        HAVING COUNT(*) = 1
    )
)
SELECT
    CONVERT(VARCHAR(100), 333000000000000 + NEXT VALUE FOR [UnitE].CAPS_ID OVER(ORDER BY REFERENCE)) AS ID
    , PROGRAMME
    , REFERENCE
    , NAME
    , TYPE
FROM UniqueRows;

方法2：用ROW_NUMBER()确保唯一行，再生成ID #

如果担心DISTINCT在复杂场景下的去重效果，可以用ROW_NUMBER()给重复行标记序号，只保留序号为1的行，再生成ID：

WITH RankedRows AS (
    SELECT
        CONVERT(VARCHAR(255), progdetail.programmecode) AS PROGRAMME
        , CONVERT(VARCHAR(50), CONCAT(modudet.code, 'CM-2024')) AS REFERENCE
        , CONVERT(VARCHAR(255), modudet.longtitle) AS NAME
        , CONVERT(VARCHAR(10), 'Module') AS TYPE
        -- 按所有需要去重的列分组，标记行号
        , ROW_NUMBER() OVER(
            PARTITION BY progdetail.programmecode, modudet.code, modudet.longtitle 
            ORDER BY modudet.code
        ) AS rn
    FROM [TABLE1] progdetail 
    JOIN [TABLE2] modudet ON progdetail.child_entitycuid = modudet.module_cuid
    WHERE progdetail.programmecode IN (
        SELECT programmecode 
        FROM TABLE2 
        GROUP BY programmecode 
        HAVING COUNT(*) = 1
    )
)
SELECT
    CONVERT(VARCHAR(100), 333000000000000 + NEXT VALUE FOR [UnitE].CAPS_ID OVER(ORDER BY REFERENCE)) AS ID
    , PROGRAMME
    , REFERENCE
    , NAME
    , TYPE
FROM RankedRows
WHERE rn = 1; -- 只保留每组第一行，确保唯一性

为什么之前的子查询方法无效？ #

如果之前尝试子查询去重后仍有重复，大概率是因为你把NEXT VALUE FOR放在了子查询内部——这样还是会给每一行（包括重复行）生成不同的ID，导致外层查询无法去重。必须把ID生成逻辑放在去重完成之后的外层查询中。

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