编写SQL查询找出每个ID所有订单共有的物料

问题描述 #

需要编写SQL查询，从给定数据表中筛选出每个ID对应的所有订单里都存在的物料。

原始数据表 #

预期查询结果 #

解决方案 #

思路 #

1. 先统计每个ID对应的唯一订单总数；
2. 再统计每个ID下，每个物料分别出现在多少个不同的订单中；
3. 筛选出物料出现的订单数等于该ID总订单数的记录，这些就是所有订单都存在的物料。

SQL查询代码 #

WITH id_order_count AS (
    SELECT 
        ID,
        COUNT(DISTINCT ORDER_ID) AS total_orders
    FROM 
        your_table_name
    GROUP BY 
        ID
),
material_order_count AS (
    SELECT 
        ID,
        MATERIAL,
        COUNT(DISTINCT ORDER_ID) AS material_orders
    FROM 
        your_table_name
    GROUP BY 
        ID, MATERIAL
)
SELECT 
    moc.ID,
    moc.MATERIAL
FROM 
    material_order_count moc
JOIN 
    id_order_count ioc ON moc.ID = ioc.ID
WHERE 
    moc.material_orders = ioc.total_orders
ORDER BY 
    moc.ID, moc.MATERIAL;

代码说明 #

• 第一个CTE id_order_count：计算每个ID拥有的唯一订单数量；
• 第二个CTE material_order_count：计算每个ID下每个物料对应的唯一订单数量；
• 最后将两个CTE关联，筛选出物料订单数等于ID总订单数的记录，得到目标结果。

内容的提问来源于stack exchange，提问作者annaa-ka