如何为相同ItemID生成相同序列号？附SQL实现示例

你的实现逻辑完全正确！ #

先给你吃个定心丸：你选择用 dense_rank() over(order by itemid) 来实现需求非常精准——dense_rank() 函数会为相同的ItemID分配完全一致的排名值，而且排名是连续递增的（不会像 rank() 那样跳过数字）。加上固定偏移量14999后，同一个ItemID对应的序列号自然完全相同，不同ItemID则得到连续的序列号，完全匹配你的预期。

我们可以看一下你的测试数据运行后的结果（完全符合需求）：

SEQ_NO  ITEMID
15000   AA-00001152
15000   AA-00001152
15001   AA-00001153
15002   AA-00001154
15002   AA-00001154
15003   AA-00001156
15003   AA-00001156
15004   BB-00001200
15005   CC-254565

更优的实现方案（针对大数据量场景） #

如果你的实际业务表数据量很大，直接在所有行上计算 dense_rank() 可能会有性能开销。可以先通过分组计算每个ItemID的序列号，再关联回原表，这样能减少排名计算的次数：

WITH item_rank_map AS (
    -- 先为每个唯一的ItemID计算序列号
    SELECT 
        itemid,
        14999 + dense_rank() OVER(ORDER BY itemid) AS seq_no
    FROM t
    GROUP BY itemid
)
-- 关联原表得到最终结果
SELECT 
    irm.seq_no,
    t.itemid
FROM t
JOIN item_rank_map irm ON t.itemid = irm.itemid
ORDER BY irm.seq_no;

这个方案的核心是先对ItemID去重，只计算一次每个ItemID的排名，再和原表关联，在数据量大时能有效提升查询效率。

额外避坑提示：函数选择的区别 #

• 别用 row_number()：它会给相同ItemID的行分配不同的序列号，直接违背你的需求；
• 别用 rank()：它会在有重复值时跳过排名（比如两个相同ItemID后，下一个排名会是3而不是2），导致序列号不连续。

所以你的初始实现已经选对了最适合的函数，逻辑完全可靠。

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