基于collection_ref统一生成状态列的SQL实现需求咨询

Hey there, let's tackle this problem step by step. First, let's recap your requirement to make sure I got it right:

若某collection_ref下存在类型为Sale且supply_chain_approved=1的记录，则该collection_ref对应的所有行在新增列中显示'Completed'；否则显示'Outstanding'

Based on your existing SQL query, here are two clean, efficient solutions to add the required status column:

方案一：使用子查询判断（逻辑直观） #

这个方案通过子查询直接检查当前collection_ref是否存在符合条件的Sale记录，避免关联表带来的重复行问题：

SELECT DISTINCT 
    a.bulk_type_code, 
    a.bulk_number, 
    a.supplier_contract_ref, 
    a.supplier_consignment_ref, 
    a.supplier_org_code, 
    a.supplier_org_name, 
    a.collection_ref, 
    a.raw_weight_tons, 
    a.financial_net_weight_tons, 
    a.purchase_weight_tons, 
    a.delivery_date, 
    b.delivery_term_code, 
    b.delivery_term_description, 
    c.week_number,
    -- 新增状态列：检查当前collection_ref是否有已批准的Sale记录
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM bi.movement m 
            WHERE m.collection_ref = a.collection_ref 
              AND m.movement_type = 'Sale' 
              AND m.supply_chain_approved = 1
        ) THEN 'Completed' 
        ELSE 'Outstanding' 
    END AS collection_status
FROM bi.bulk_subcontainer_pricing_group_summary a 
LEFT JOIN bi.contracts b ON a.supplier_contract_number = b.contract_number OR a.purchaser_contract_number = b.contract_number 
INNER JOIN bi.weeks c ON a.delivery_date BETWEEN c.start_date AND c.end_date
WHERE a.delivery_date BETWEEN @DFrom AND @DTo 
  AND a.bulk_type_code IN (@BulkType) 
  AND a.business_unit_code = 'OLLIMP' 
  AND a.pricing_type_group_code = @pricing_hidden 
ORDER BY a.collection_ref;

方案优势： #

• 逻辑清晰，直接对应需求描述
• 无需关联movement表到主查询，减少不必要的行重复，性能更优

方案二：使用窗口函数（适合需复用movement数据的场景） #

如果后续需要用到movement表的其他字段，这个方案通过窗口函数批量判断状态，扩展性更强：

SELECT DISTINCT 
    a.bulk_type_code, 
    a.bulk_number, 
    a.supplier_contract_ref, 
    a.supplier_consignment_ref, 
    a.supplier_org_code, 
    a.supplier_org_name, 
    a.collection_ref, 
    a.raw_weight_tons, 
    a.financial_net_weight_tons, 
    a.purchase_weight_tons, 
    a.delivery_date, 
    b.delivery_term_code, 
    b.delivery_term_description, 
    c.week_number,
    -- 新增状态列：通过窗口函数判断当前collection_ref的整体状态
    CASE 
        WHEN MAX(CASE WHEN m.movement_type = 'Sale' AND m.supply_chain_approved = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY a.collection_ref) = 1 
        THEN 'Completed' 
        ELSE 'Outstanding' 
    END AS collection_status
FROM bi.bulk_subcontainer_pricing_group_summary a 
LEFT JOIN bi.contracts b ON a.supplier_contract_number = b.contract_number OR a.purchaser_contract_number = b.contract_number 
INNER JOIN bi.weeks c ON a.delivery_date BETWEEN c.start_date AND c.end_date
LEFT JOIN bi.movement m ON a.collection_ref = m.collection_ref
WHERE a.delivery_date BETWEEN @DFrom AND @DTo 
  AND a.bulk_type_code IN (@BulkType) 
  AND a.business_unit_code = 'OLLIMP' 
  AND a.pricing_type_group_code = @pricing_hidden 
ORDER BY a.collection_ref;

方案优势： #

• 一次性关联movement表，后续可直接复用该表的其他字段
• 窗口函数PARTITION BY确保对每个collection_ref统一判断状态

两种方案都能完美满足你的需求，你可以根据实际业务场景选择合适的版本~

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