如何在SQL中使用窗口函数生成带有层级结构的财务流水号？

应使用ROW_NUMBER() OVER (PARTITION BY business_type ORDER BY create_time, id)生成分组连续流水号，配合日期前缀与补零拼接，避免GROUP BY误用、并发冲突及NULL导致的串号问题。

流水号需要按业务类型分组连续编号，用 PARTITION BY 而不是 GROUP BY

直接在 SELECT 中对原始流水表用 ROW_NUMBER() OVER (PARTITION BY business_type ORDER BY create_time) 即可生成每类业务独立的序号。注意不能先 GROUP BY 再套窗口函数——那会丢失明细行，导致编号对象错误。

常见错误是写成：SELECT business_type, ROW_NUMBER() OVER (ORDER BY create_time) FROM ledger GROUP BY business_type，这既语法报错（create_time 未聚合），也违背“为每条流水生成编号”的初衷。

• PARTITION BY 定义编号边界，比如把 'revenue' 和 'expense' 分开计数
• ORDER BY 必须明确且稳定，建议至少包含 create_time 和主键（如 id），避免因时间精度相同导致排序不确定
• 若业务要求“同日流水号从 001 开始”，需确保 create_time 精确到秒或毫秒，否则可能跨天重复

流水号要带日期前缀（如 REVENUE-20240520-001），用 TO_CHAR 或 FORMAT 拼接

PostgreSQL 用 TO_CHAR(create_time, 'YYYYMMDD')，MySQL 8.0+ 用 DATE_FORMAT(create_time, '%Y%m%d')，SQL Server 用 FORMAT(create_time, 'yyyyMMdd')。拼接时务必用 LPAD(ROW_NUMBER(), 3, '0') 补零，别用字符串重复拼接——性能差还易出错。

示例（PostgreSQL）：business_type || '-' || TO_CHAR(create_time, 'YYYYMMDD') || '-' || LPAD(ROW_NUMBER() OVER (PARTITION BY business_type, DATE(create_time) ORDER BY create_time, id), 3, '0')

• 层级结构中的“日期段”必须和编号逻辑一致：如果按日重置序号，PARTITION BY 就得包含 DATE(create_time)
• MySQL 5.7 不支持窗口函数，强行用变量模拟（@row := IF(@type = business_type AND DATE(@dt) = DATE(create_time), @row + 1, 1)）极易在并发或优化器重排后错乱，不推荐
• 拼接结果是文本，无法直接参与数值计算；如后续需提取序号做范围查询，应单独存整型字段

多级嵌套结构（如 REVENUE-20240520-A001）需要额外分类维度，靠 CASE WHEN + 窗口函数组合

字母后缀（A/B/C）通常对应子业务线或审批等级，不能硬编码进 PARTITION BY。正确做法是先用 CASE WHEN 计算出逻辑分组字段，再作为 PARTITION BY 输入。

例如：PARTITION BY business_type, DATE(create_time), CASE WHEN amount >= 10000 THEN 'A' WHEN amount >= 5000 THEN 'B' ELSE 'C' END

• 嵌套层级越多，PARTITION BY 列越多，索引需覆盖全部列才能高效排序
• 分类逻辑若涉及子查询或 UDF，会显著拖慢窗口函数执行——这类计算尽量提前物化到临时列
• 注意 NULL 处理：CASE 结果为 NULL 时，所有该类记录会被归入同一分区，导致编号串号

生产环境必须考虑并发插入下的编号唯一性

窗口函数只读不锁，纯 SQL 方案无法保证高并发下流水号绝对不重复。即使加了唯一索引，冲突发生时仍要靠应用层重试或数据库序列兜底。

• 最稳妥方式是用数据库序列（如 PostgreSQL nextval('ledger_seq')）生成核心序号，窗口函数仅用于格式化展示
• 若坚持全靠窗口函数，必须在事务中先 SELECT ... FOR UPDATE 锁定当日/当类最大编号行，再查出下一个值——但会严重降低吞吐
• 流水号本质是业务标识，不是主键；真正防重应依赖主键或唯一约束，而非编号生成逻辑本身

层级越深、规则越细，动态生成的不可控因素越多。上线前务必用真实数据量压测编号生成路径，尤其关注跨日、跨类边界时刻的输出一致性。