如何基于production_date字段批量生成batch_code？附SQL查询语句

看起来你想要基于production_date字段为查询结果批量生成batch_code，我结合常见的业务场景，给你提供几种不同数据库下的实现方案，你可以根据自己使用的数据库选择：

1. 仅基于日期生成基础批次号 #

如果只需要以production_date的日期部分作为批次号核心，格式化为YYYYMMDD这类固定字符串即可：

MySQL/MariaDB版本 #

SELECT 
    receiving_detail.con_id, 
    receiving_detail.ITEM_ID, 
    receiving_detail.TRANSACTION_QTY, 
    receiving_detail.UNIT_COST, 
    receiving_detail.UOM_ID, 
    item_master_list.item_code, 
    receiving_detail.po_item_id, 
    receiving_detail.production_date, 
    receiving_detail.expiration_date,
    -- 生成YYYYMMDD格式的批次号
    DATE_FORMAT(receiving_detail.production_date, '%Y%m%d') AS batch_code
FROM receiving_detail 
LEFT JOIN item_master_list ON item_master_list.id = receiving_detail.item_id;

PostgreSQL版本 #

SELECT 
    receiving_detail.con_id, 
    receiving_detail.ITEM_ID, 
    receiving_detail.TRANSACTION_QTY, 
    receiving_detail.UNIT_COST, 
    receiving_detail.UOM_ID, 
    item_master_list.item_code, 
    receiving_detail.po_item_id, 
    receiving_detail.production_date, 
    receiving_detail.expiration_date,
    -- 生成YYYYMMDD格式的批次号
    TO_CHAR(receiving_detail.production_date, 'YYYYMMDD') AS batch_code
FROM receiving_detail 
LEFT JOIN item_master_list ON item_master_list.id = receiving_detail.item_id;

SQL Server版本 #

SELECT 
    receiving_detail.con_id, 
    receiving_detail.ITEM_ID, 
    receiving_detail.TRANSACTION_QTY, 
    receiving_detail.UNIT_COST, 
    receiving_detail.UOM_ID, 
    item_master_list.item_code, 
    receiving_detail.po_item_id, 
    receiving_detail.production_date, 
    receiving_detail.expiration_date,
    -- 生成YYYYMMDD格式的批次号
    CONVERT(VARCHAR(8), receiving_detail.production_date, 112) AS batch_code
FROM receiving_detail 
LEFT JOIN item_master_list ON item_master_list.id = receiving_detail.item_id;

2. 结合物品编码+日期生成唯一批次号 #

如果需要更具辨识度的唯一批次号，可以把item_code和日期拼接，比如格式为物品编码_YYYYMMDD：

MySQL/MariaDB示例 #

SELECT 
    receiving_detail.con_id, 
    receiving_detail.ITEM_ID, 
    receiving_detail.TRANSACTION_QTY, 
    receiving_detail.UNIT_COST, 
    receiving_detail.UOM_ID, 
    item_master_list.item_code, 
    receiving_detail.po_item_id, 
    receiving_detail.production_date, 
    receiving_detail.expiration_date,
    -- 拼接物品编码与日期
    CONCAT(item_master_list.item_code, '_', DATE_FORMAT(receiving_detail.production_date, '%Y%m%d')) AS batch_code
FROM receiving_detail 
LEFT JOIN item_master_list ON item_master_list.id = receiving_detail.item_id;

PostgreSQL示例 #

SELECT 
    receiving_detail.con_id, 
    receiving_detail.ITEM_ID, 
    receiving_detail.TRANSACTION_QTY, 
    receiving_detail.UNIT_COST, 
    receiving_detail.UOM_ID, 
    item_master_list.item_code, 
    receiving_detail.po_item_id, 
    receiving_detail.production_date, 
    receiving_detail.expiration_date,
    -- PostgreSQL用||做字符串拼接
    item_master_list.item_code || '_' || TO_CHAR(receiving_detail.production_date, 'YYYYMMDD') AS batch_code
FROM receiving_detail 
LEFT JOIN item_master_list ON item_master_list.id = receiving_detail.item_id;

SQL Server示例 #

SELECT 
    receiving_detail.con_id, 
    receiving_detail.ITEM_ID, 
    receiving_detail.TRANSACTION_QTY, 
    receiving_detail.UNIT_COST, 
    receiving_detail.UOM_ID, 
    item_master_list.item_code, 
    receiving_detail.po_item_id, 
    receiving_detail.production_date, 
    receiving_detail.expiration_date,
    -- SQL Server用+做字符串拼接
    item_master_list.item_code + '_' + CONVERT(VARCHAR(8), receiving_detail.production_date, 112) AS batch_code
FROM receiving_detail 
LEFT JOIN item_master_list ON item_master_list.id = receiving_detail.item_id;

3. 日期+物品编码+流水号生成唯一批次号 #

如果同一天同一物品可能存在多个批次，需要添加流水号来区分，可以用窗口函数实现：

通用版本（适配MySQL 8.0+/PostgreSQL/SQL Server） #

SELECT 
    con_id, 
    ITEM_ID, 
    TRANSACTION_QTY, 
    UNIT_COST, 
    UOM_ID, 
    item_code, 
    po_item_id, 
    production_date, 
    expiration_date,
    -- 格式：YYYYMMDD_物品编码_流水号
    CONCAT(
        DATE_FORMAT(production_date, '%Y%m%d'), -- PostgreSQL替换为TO_CHAR(production_date, 'YYYYMMDD')，SQL Server替换为CONVERT(VARCHAR(8), production_date, 112)
        '_',
        item_code,
        '_',
        ROW_NUMBER() OVER(PARTITION BY item_code, DATE(production_date) ORDER BY con_id)
    ) AS batch_code
FROM (
    SELECT 
        receiving_detail.con_id, 
        receiving_detail.ITEM_ID, 
        receiving_detail.TRANSACTION_QTY, 
        receiving_detail.UNIT_COST, 
        receiving_detail.UOM_ID, 
        item_master_list.item_code, 
        receiving_detail.po_item_id, 
        receiving_detail.production_date, 
        receiving_detail.expiration_date
    FROM receiving_detail 
    LEFT JOIN item_master_list ON item_master_list.id = receiving_detail.item_id
) AS temp;

这里的PARTITION BY item_code, DATE(production_date)表示按物品编码和日期分组，每组内生成从1开始的流水号，确保同一天同一物品的批次号唯一。

如果你有特定的batch_code格式要求（比如固定前缀、特定长度、包含其他字段等），可以补充说明规则，我再帮你调整代码~

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