产品销售记录提交错误与可用库存计算异常排查求助

问题描述 #

存在产品销售记录提交错误、可用库存计算不准确的问题，核心关联表为tbl_order与tbl_stock_product。其中model、size、color字段随产品类型可为空，需按code_unique及所属企业（id_enterprise）或门店（id_branch_office）进行关联。

现有SQL已实现大部分功能，但total_sales和stock计算存在以下问题：

• total_sales（对应原查询的t.quantity）：不同规格、颜色的产品会重复扣除相同销量；无销售记录时未显示0。
• stock计算：无销售记录时无结果，期望直接显示总可用库存。
  问题源于tbl_order表的关联逻辑，尝试处理NULL值匹配后仍未解决，需得到符合指定结果的正确SQL查询方案。

核心表结构与数据 #

tbl_order表 #

tbl_stock_product表 #

现有查询代码 #

select office_establishment,
       office_tradename,
       code_unique,
       model,
       size,
       color,
       sum(item_total) as item_total,
       sum(quantity)   as quantity,
       sum(stock)      as stock
from (
         SELECT bo.establishment                          AS office_establishment,
                bo.tradename                              AS office_tradename,
                sp.code_unique,
                sp.model,
                sp.size,
                sp.color,
                item_total,
                SUM(ifnull(odr.quantity, 0))              AS quantity,
                item_total - sum(ifnull(odr.quantity, 0)) as stock
         FROM tbl_stock_product sp
                  LEFT JOIN tbl_access ac
                            ON sp.id_enterprise = ac.id_enterprise OR sp.id_branch_office = ac.id_branch_office
                  LEFT JOIN tbl_enterprise ent ON sp.id_enterprise = ent.id_enterprise
                  LEFT JOIN tbl_branch_office bo ON sp.id_branch_office = bo.id_branch_office
                  LEFT JOIN tbl_order odr on odr.id_product = sp.id_stock_product
         WHERE ac.id_user = 2
         GROUP BY ent.establishment, bo.establishment, bo.tradename, sp.code_unique, sp.model, sp.size, sp.color,
                  sp.id_stock_product, sp.item_total
     ) t
group by office_establishment, office_tradename, code_unique, model, size, color

尝试修改的关联条件 #

LEFT JOIN tbl_order odr
    ON sp.code_unique = odr.code_unique
    AND (sp.id_enterprise = odr.id_enterprise OR sp.id_branch_office = odr.id_branch_office)
    AND ((sp.model IS NULL OR odr.model IS NULL) OR sp.model = odr.model)
    AND ((sp.size IS NULL OR odr.size IS NULL) OR sp.size = odr.size)
    AND ((sp.color IS NULL OR odr.color IS NULL) OR sp.color = odr.color)

期望结果 #

正确SQL方案 #

SELECT
    sp.code_unique,
    sp.model,
    sp.size,
    sp.color,
    SUM(sp.item_total) AS item_total,
    COALESCE(odr.total_sales, 0) AS total_sales,
    SUM(sp.item_total) - COALESCE(odr.total_sales, 0) AS stock
FROM tbl_stock_product sp
LEFT JOIN tbl_access ac
    ON sp.id_enterprise = ac.id_enterprise OR sp.id_branch_office = ac.id_branch_office
LEFT JOIN (
    -- 先按产品维度聚合销量，避免关联后重复计算
    SELECT
        code_unique,
        id_enterprise,
        id_branch_office,
        model,
        size,
        color,
        SUM(quantity) AS total_sales
    FROM tbl_order
    GROUP BY code_unique, id_enterprise, id_branch_office, model, size, color
) odr
    ON sp.code_unique = odr.code_unique
    AND (sp.id_enterprise = odr.id_enterprise OR sp.id_branch_office = odr.id_branch_office)
    AND (sp.model <=> odr.model) -- 处理NULL值相等匹配
    AND (sp.size <=> odr.size)
    AND (sp.color <=> odr.color)
WHERE ac.id_user = 2
GROUP BY sp.code_unique, sp.model, sp.size, sp.color;

方案说明 #

1. 预聚合订单数据：通过子查询先对tbl_order按产品维度（code_unique、企业/门店、规格颜色）聚合销量，避免一对多关联导致的重复求和问题。
2. 简化NULL匹配逻辑：使用<=>操作符替代复杂的NULL判断，该操作符可直接识别NULL值相等（NULL <=> NULL返回true）。
3. 处理无销售场景：用COALESCE函数确保无销售记录时total_sales显示0，库存直接为总库存减去0。

内容的提问来源于stack exchange，提问作者J. Mick