MySQL分组查询中自增列序号不连续问题求助

这个问题我碰到过不少次，核心原因是你把自增变量的计算放在了聚合操作之前——@i:=@i+1是在contract和contractuser关联后的每一行就执行了，而GROUP BY会把同一ContractNo的多行合并成一行，这时候原来的序号就会跳过那些被合并的行，自然就不连续了。

解决方案1：先聚合再加自增列（兼容MySQL 5.x版本） #

我们可以先把聚合后的结果作为一个子查询，然后在外层再用自增变量生成连续的序号：

SELECT @i:=@i+1 AS Sno, t.*
FROM (
    SELECT 
        a.ContractNo,
        a.SoftwareName,
        a.CompanyName,
        b.InvoiceNo,
        b.InvoiceDate,
        b.InvAmount,
        b.InvoicePF,
        MAX(b.InvoicePT) AS InvoicePeriodTo,
        b.InvoiceRD,
        b.ISD
    FROM contract as a
    JOIN contractuser as b ON a.ContractNo = b.ContractNo
    GROUP BY b.ContractNo, a.SoftwareName, a.CompanyName, b.InvoiceNo, b.InvoiceDate, b.InvAmount, b.InvoicePF, b.InvoiceRD, b.ISD
    ORDER BY a.SoftwareName ASC
) AS t, (SELECT @i:=0) AS i;

注意：如果你的MySQL开启了ONLY_FULL_GROUP_BY模式（默认开启），GROUP BY子句必须包含所有非聚合的字段，不然会报错。上面的查询已经把SELECT里除了MAX(b.InvoicePT)之外的字段都加入了GROUP BY。

解决方案2：使用窗口函数（MySQL 8.0+推荐） #

如果你的MySQL版本是8.0及以上，用ROW_NUMBER()窗口函数会更简洁，也不需要手动维护变量：

SELECT 
    ROW_NUMBER() OVER (ORDER BY a.SoftwareName ASC) AS Sno,
    a.ContractNo,
    a.SoftwareName,
    a.CompanyName,
    b.InvoiceNo,
    b.InvoiceDate,
    b.InvAmount,
    b.InvoicePF,
    MAX(b.InvoicePT) AS InvoicePeriodTo,
    b.InvoiceRD,
    b.ISD
FROM contract as a
JOIN contractuser as b ON a.ContractNo = b.ContractNo
GROUP BY b.ContractNo, a.SoftwareName, a.CompanyName, b.InvoiceNo, b.InvoiceDate, b.InvAmount, b.InvoicePF, b.InvoiceRD, b.ISD
ORDER BY a.SoftwareName ASC;

ROW_NUMBER()会直接基于最终聚合后的结果集，按照你指定的排序规则生成连续的序号，完全避免了变量带来的顺序问题。

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