左连接厂商与产品表后，如何按条件设置cost_complete状态标识？

问题核心 #

要给每个厂商添加cost_complete状态：

• 当厂商所有产品的cost字段均大于0（无null或0值），标识为true
• 只要存在任意产品cost为0或未设置（null），标识为false
  之前的错误在于按单条产品判断状态，导致标识被每条数据覆盖，需切换为厂商维度的整体判断。

方案1：仅需厂商汇总数据（无产品明细） #

直接按厂商分组聚合，统计不符合条件的产品数量：

SELECT 
    m.manufacturer_id,
    m.manufacturer_name,
    SUM(p.qty) AS total_qty,
    SUM(p.price * p.qty) AS total_price,
    SUM(p.cost * p.qty) AS total_cost,
    -- 统计该厂商下cost不合格的产品数，为0则所有产品都合格
    CASE 
        WHEN COUNT(CASE WHEN p.cost IS NULL OR p.cost <= 0 THEN 1 END) = 0 
        THEN 'true' 
        ELSE 'false' 
    END AS cost_complete
FROM manufacturers m
LEFT JOIN products p ON m.manufacturer_id = p.manufacturer_id
GROUP BY m.manufacturer_id, m.manufacturer_name;

方案2：保留产品明细，同时显示厂商级状态 #

用窗口函数按厂商分区，整体判断状态：

SELECT 
    m.manufacturer_id,
    m.manufacturer_name,
    p.product_id,
    p.qty,
    p.price,
    p.cost,
    SUM(p.qty) OVER (PARTITION BY m.manufacturer_id) AS total_qty,
    SUM(p.price * p.qty) OVER (PARTITION BY m.manufacturer_id) AS total_price,
    SUM(p.cost * p.qty) OVER (PARTITION BY m.manufacturer_id) AS total_cost,
    -- 检查厂商下是否存在不合格的cost，存在则标识为false
    CASE 
        WHEN MAX(CASE WHEN p.cost IS NULL OR p.cost <= 0 THEN 1 ELSE 0 END) OVER (PARTITION BY m.manufacturer_id) = 0
        THEN 'true'
        ELSE 'false'
    END AS cost_complete
FROM manufacturers m
LEFT JOIN products p ON m.manufacturer_id = p.manufacturer_id;

关键说明 #

• 方案1通过GROUP BY将数据聚合到厂商维度，确保状态是厂商整体的判断结果
• 方案2用OVER (PARTITION BY m.manufacturer_id)实现厂商内的窗口计算，既保留每条产品明细，又能得到统一的厂商状态
• 两种方案都通过嵌套CASE先标记单条产品的cost是否合格，再通过聚合/窗口函数判断厂商整体情况，避免单条数据覆盖状态的问题

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