如何查询仅包含Lipstick的所有完整订单记录

需求说明 #

从item table中查询仅包含Lipstick这一种商品的所有完整订单对应的全部行记录。

现有示例表结构（item_table） #

预期输出 #

实现SQL语句 #

方法1：子查询+分组过滤（兼容性最好，支持所有主流数据库） #

SELECT *
FROM item_table
WHERE OrderId IN (
    SELECT OrderId
    FROM item_table
    GROUP BY OrderId
    -- 校验订单内没有除Lipstick外的其他商品
    HAVING SUM(CASE WHEN ProductName != 'Lipstick' THEN 1 ELSE 0 END) = 0
)

方法2：窗口函数（写法更简洁，支持MySQL8.0+/PostgreSQL/Oracle等支持窗口函数的数据库） #

SELECT ProductId, ProductName, OrderId
FROM (
    SELECT 
        *,
        -- 按订单分组统计非Lipstick商品的数量
        SUM(CASE WHEN ProductName != 'Lipstick' THEN 1 ELSE 0 END) OVER (PARTITION BY OrderId) AS invalid_product_count
    FROM item_table
) t
WHERE invalid_product_count = 0

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