题解 | 评估不同供应商提供的零部件质量和成本情况

WITH tb1 AS(
    SELECT suppliers.supplier_id, 
           supply_quality_cost.component_id,
           suppliers.supplier_name,
           supply_quality_cost.quality_score,
           supply_quality_cost.cost
    FROM supply_quality_cost
    LEFT JOIN suppliers 
    ON supply_quality_cost.supplier_id = suppliers.supplier_id
    WHERE quality_score > 80 AND cost < 50
    ORDER BY supply_id ASC, cost ASC
)

SELECT tb1.supplier_id,
       tb1.supplier_name,
       components.component_name,
       tb1.quality_score,
       tb1.cost
FROM tb1
LEFT JOIN components ON tb1.component_id = components.component_id
ORDER BY supplier_id ASC, cost ASC