SQL实现lineid='01'时docid自增的发票明细分单方法

发票明细表现有lineid（行标识）、price €（欧元价格）两个字段，业务规则如下：

• lineid='01'为新发票的起始行，每出现一次该值代表进入新发票，需生成新的docid（单据ID）
• 同一张发票的非起始行，沿用当前发票的docid
  原有尝试的Case when lineid='01' then newid else id逻辑无法满足需求，需对应SQL实现方案。

原始样例数据 #

期望输出 #

注意：SQL按顺序处理数据必须依赖明确的排序字段，建议先给表添加自增主键/入库时间字段作为排序依据，避免数据库返回数据顺序错乱导致docid生成错误。

支持窗口函数的数据库（MySQL8.0+、PostgreSQL、SQL Server、Oracle12c+等） #

核心逻辑是按固定顺序逐行统计截至当前行lineid='01'的累计出现次数，该累计值即为符合规则的docid：

-- 假设表名为invoice_details，请将下方SQL中的<排序字段名>替换为实际用来固定行顺序的字段（如自增id、入库时间）
SELECT
    SUM(CASE WHEN lineid = '01' THEN 1 ELSE 0 END) OVER (
        ORDER BY <排序字段名>
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS docid,
    lineid,
    `price €`
FROM invoice_details;

不支持窗口函数的老版本（如MySQL5.x） #

通过用户变量逐行累计计数实现相同效果：

-- 先初始化计数变量
SET @doc_id := 0;
-- 逐行遍历生成docid，同样需要替换<排序字段名>
SELECT
    @doc_id := @doc_id + CASE WHEN lineid = '01' THEN 1 ELSE 0 END AS docid,
    lineid,
    `price €`
FROM invoice_details
ORDER BY <排序字段名>;

原逻辑问题说明 #

之前编写的Case when lineid='01' then newid else id逻辑未做累计计数，非起始行无法继承上一行的docid值，因此无法实现同发票ID沿用的效果。

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