如何查询数据库中出现多次的order_id与product_id（购物篮分析）

要实现保留出现次数不止一次的order_id和product_id对应的记录（即同时满足order_id在表中出现≥2次、product_id在表中出现≥2次），并按order_id排序的需求，可以通过子查询或CTE（公共表表达式）筛选有效ID集合，再关联原表得到结果。

方法1：使用子查询（兼容大多数数据库） #

SELECT order_id, product_id
FROM order_items
WHERE 
    order_id IN (
        SELECT order_id
        FROM order_items
        GROUP BY order_id
        HAVING COUNT(*) > 1
    )
    AND product_id IN (
        SELECT product_id
        FROM order_items
        GROUP BY product_id
        HAVING COUNT(*) > 1
    )
ORDER BY order_id;

方法2：使用CTE（可读性更强，支持PostgreSQL、MySQL 8.0+等） #

WITH valid_orders AS (
    SELECT order_id
    FROM order_items
    GROUP BY order_id
    HAVING COUNT(*) > 1
),
valid_products AS (
    SELECT product_id
    FROM order_items
    GROUP BY product_id
    HAVING COUNT(*) > 1
)
SELECT oi.order_id, oi.product_id
FROM order_items oi
JOIN valid_orders vo ON oi.order_id = vo.order_id
JOIN valid_products vp ON oi.product_id = vp.product_id
ORDER BY oi.order_id;

逻辑说明 #

1. 筛选有效order_id：通过分组统计，找出所有出现次数超过1次的order_id；
2. 筛选有效product_id：同样分组统计，找出所有出现次数超过1次的product_id；
3. 关联筛选：只保留原表中同时属于有效order_id和有效product_id的记录，最后按order_id排序。

对照你的数据示例：

• order_id=4仅出现1次，会被排除；
• product_id=4、5仅出现1次，对应记录会被排除；
• 最终结果与目标输出完全一致。

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