zkxw/docs/sql/migrations/2026-10-08-sample-freight-no.sql

-- 运费批次号：YF + YYYYMMDD + 001～999（按租户、按日递增）

ALTER TABLE biz_sample_freight
  ADD COLUMN freight_no VARCHAR(32) NOT NULL DEFAULT '' COMMENT '运费批次号 YF+YYYYMMDD+序号' AFTER id;

-- 按租户 + 创建日回填历史数据
SET @grp := '';
SET @seq := 0;

UPDATE biz_sample_freight f
INNER JOIN (
  SELECT
    id,
    CONCAT(
      'YF',
      DATE_FORMAT(create_time, '%Y%m%d'),
      LPAD(
        @seq := IF(
          @grp = CONCAT(tenant_no, '|', DATE_FORMAT(create_time, '%Y%m%d')),
          @seq + 1,
          1
        ),
        3,
        '0'
      )
    ) AS new_no,
    @grp := CONCAT(tenant_no, '|', DATE_FORMAT(create_time, '%Y%m%d')) AS _grp
  FROM biz_sample_freight
  ORDER BY tenant_no ASC, create_time ASC, id ASC
) t ON f.id = t.id
SET f.freight_no = t.new_no
WHERE f.freight_no = '' OR f.freight_no IS NULL;

ALTER TABLE biz_sample_freight
  ADD UNIQUE KEY uk_sample_freight_tenant_no (tenant_no, freight_no);