如何为SQL查询结果添加按颜色分组的重复计数列？

最优方案：使用窗口函数ROW_NUMBER() #

不需要用临时表循环，SQL原生的窗口函数就能高效实现需求，这是行业通用的标准解法。

示例数据 #

原始表menu的数据：

color    flavor
-----    ------
red      cherry
green    lime
red      strawberry
white    tapioca
blue     blueberry
white    vanilla

实现查询语句 #

SELECT 
    color,
    flavor,
    ROW_NUMBER() OVER (PARTITION BY color ORDER BY flavor) AS group_seq
FROM menu
ORDER BY color, group_seq;

结果说明 #

• PARTITION BY color：按color字段分组，每个颜色组内单独计数
• ORDER BY flavor：指定组内的排序规则（可根据需求换成其他字段，比如插入时间等）
• 最终结果会按颜色分组，组内生成从1开始的连续序号，与期望结果一致：

red      cherry       1
red      strawberry   2
white    tapioca      1
white    vanilla      2
green    lime         1
blue     blueberry    1

（注：如果需要和你给出的white组顺序完全匹配，把ORDER BY flavor换成ORDER BY CASE flavor WHEN 'vanilla' THEN 1 ELSE 2 END即可，或根据实际业务逻辑调整排序字段）

关于临时表循环思路的补充 #

你的循环思路可行但效率极低，数据量较大时不建议使用。如果一定要补全伪代码，参考以下（以SQL Server为例）：

DECLARE @color VARCHAR(50), @flavor VARCHAR(50), @current_color VARCHAR(50), @seq INT = 1
DECLARE @tmp TABLE (color VARCHAR(50), flavor VARCHAR(50))
DECLARE @result TABLE (color VARCHAR(50), flavor VARCHAR(50), group_seq INT)

-- 插入原始数据到临时表
INSERT INTO @tmp
SELECT color, flavor FROM menu

-- 获取第一条数据
SELECT TOP 1 @color = color, @flavor = flavor FROM @tmp
SET @current_color = @color

WHILE @@ROWCOUNT > 0
BEGIN
    -- 判断当前颜色是否和上一个相同，相同则序号+1，否则重置为1
    IF @color = @current_color
        SET @seq = @seq + 1
    ELSE
    BEGIN
        SET @current_color = @color
        SET @seq = 1
    END

    -- 插入结果表
    INSERT INTO @result (color, flavor, group_seq)
    VALUES (@color, @flavor, @seq)

    -- 删除当前处理的行
    DELETE TOP (1) FROM @tmp WHERE color = @color AND flavor = @flavor

    -- 获取下一条数据
    SELECT TOP 1 @color = color, @flavor = flavor FROM @tmp
END

-- 输出结果
SELECT * FROM @result ORDER BY color, group_seq

再次强调，逐行处理的方式远不如窗口函数高效，生产环境优先使用窗口函数。

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