按列分组生成自增transactionid的SQL实现需求

你的需求是给mini_sales表中**同一组（totalpayment、totalchange、date完全相同）**的所有行分配同一个自增的transactionid，不同组依次递增。你之前写的GROUP BY语句无法实现这个需求，原因是：

• GROUP BY会将每组数据合并为一行返回，无法保留原表的所有行记录
• SELECT *与GROUP BY混用属于不规范写法（多数数据库会报错或返回不可预期的结果），根本无法给每行分配分组编号

解决方案1：查询时生成transactionid（不修改原表） #

如果只是需要查询结果中显示分组编号，可以用窗口函数DENSE_RANK()，它能为相同分组分配一致的编号，且编号不会跳号：

SELECT 
    id,
    DENSE_RANK() OVER (ORDER BY date, totalpayment, totalchange) AS transactionid,
    totalpayment,
    totalchange,
    date
FROM mini_sales
ORDER BY id;

• OVER (ORDER BY date, totalpayment, totalchange)：指定分组的排序依据，确保相同组的记录被归为一类
• DENSE_RANK()：为每个不同的分组分配递增的编号，同一组内编号相同

解决方案2：更新原表的transactionid字段 #

如果需要永久修改表中的transactionid字段，根据数据库版本可以用以下两种方式：

方式一：支持CTE（如MySQL 8.0+、PostgreSQL、SQL Server）

WITH grouped_trans AS (
    SELECT 
        totalpayment, 
        totalchange, 
        date,
        DENSE_RANK() OVER (ORDER BY date, totalpayment, totalchange) AS trans_id
    FROM mini_sales
    GROUP BY totalpayment, totalchange, date
)
UPDATE mini_sales s
JOIN grouped_trans g 
    ON s.totalpayment = g.totalpayment 
    AND s.totalchange = g.totalchange 
    AND s.date = g.date
SET s.transactionid = g.trans_id;

方式二：不支持CTE（如MySQL 5.x）

用变量来生成自增编号：

UPDATE mini_sales s
JOIN (
    SELECT 
        totalpayment, 
        totalchange, 
        date,
        @trans_rank := @trans_rank + 1 AS trans_id
    FROM (
        -- 先获取所有唯一的分组并排序
        SELECT DISTINCT totalpayment, totalchange, date
        FROM mini_sales
        ORDER BY date, totalpayment, totalchange
    ) AS unique_groups,
    -- 初始化变量
    (SELECT @trans_rank := 0) AS rank_init
) g 
    ON s.totalpayment = g.totalpayment 
    AND s.totalchange = g.totalchange 
    AND s.date = g.date
SET s.transactionid = g.trans_id;

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