如何合并销售与采购查询并生成日期维度购销扣减报表？

需求概述 #

从Core表分别提取采购、销售数据，需合并为单一查询并生成日期维度报表，核心逻辑为同一日期的销售数量从对应采购数量中扣除。

现有问题分析 #

你当前分开查询采购和销售数据并单独分组，无法直接在数据库层面完成日期匹配与数量扣除，需通过UNION合并数据集后再做聚合计算。

解决方案代码 #

$combinedData = DB::table(DB::raw('(
    -- 采购数据：标记为正数量
    SELECT
        cores.model_code,
        vehicles.model,
        cores.mushak_date AS transaction_date,
        MONTH(cores.mushak_date) AS month,
        cores.vat_code,
        cores.uml_mushak_no,
        cores.vat_rebate,
        NULL AS customer_name,
        NULL AS sale_mushak_no,
        NULL AS sale_vat,
        1 AS quantity,
        "purchase" AS transaction_type
    FROM cores
    RIGHT JOIN vehicles ON vehicles.model_code = cores.model_code
    WHERE cores.vat_code = ?
      AND cores.mushak_date BETWEEN ? AND ?
    
    UNION ALL
    
    -- 销售数据：标记为负数量，方便后续直接求和扣除
    SELECT
        cores.model_code,
        vehicles.model,
        cores.vat_sale_date AS transaction_date,
        MONTH(cores.vat_sale_date) AS month,
        cores.vat_code,
        cores.uml_mushak_no,
        NULL AS vat_rebate,
        cores.customer_name,
        cores.sale_mushak_no,
        cores.sale_vat,
        -1 AS quantity,
        "sale" AS transaction_type
    FROM cores
    RIGHT JOIN vehicles ON vehicles.model_code = cores.model_code
    WHERE cores.vat_code = ?
      AND cores.vat_sale_date BETWEEN ? AND ?
) AS combined'))
->select(
    'model_code',
    'model',
    'transaction_date',
    'month',
    'vat_code',
    'uml_mushak_no',
    DB::raw('SUM(quantity) AS net_quantity'),
    DB::raw('MAX(vat_rebate) AS vat_rebate'),
    DB::raw('GROUP_CONCAT(DISTINCT customer_name) AS customers'),
    DB::raw('GROUP_CONCAT(DISTINCT sale_mushak_no) AS sale_mushaks')
)
->setBindings([$vat_code, $start_date, $end_date, $vat_code, $start_date, $end_date])
->groupBy(['model', 'transaction_date', 'month', 'model_code', 'vat_code', 'uml_mushak_no'])
->orderBy('transaction_date', 'asc')
->get();

关键逻辑说明 #

• 数据合并：用UNION ALL将采购、销售数据合并，统一字段结构，采购数量设为1，销售数量设为-1
• 日期对齐：将采购的mushak_date和销售的vat_sale_date统一命名为transaction_date，确保日期维度匹配
• 聚合计算：按model、transaction_date等维度分组，通过SUM(quantity)直接得到采购扣除销售后的净数量
• 字段兼容：为两边不存在的字段设为NULL，保证UNION结构一致；用GROUP_CONCAT聚合销售侧的客户、单据号等信息

后续处理 #

如果需要按月份汇总，只需调整groupBy为['model', 'month', 'model_code', 'vat_code', 'uml_mushak_no']，同时将transaction_date替换为month进行统计即可。

内容的提问来源于stack exchange，提问作者Md Monirul Islam