SQL查询同order_id和product_id重复订单项记录的实现方法

待处理表为stg_83087_wc_order_product_lookup，表内测试数据如下：

order_item_id order_id product_id
1                513     120
2                213     121
3                513     120
4                312     131
5                312     131
6                102     123

需要筛选出order_id和product_id组合重复出现的所有行，预期返回结果：

order_item_id order_id product_id
1                513     120
3                513     120
4                312     131
5                312     131

原SQL无效原因：WHERE条件中写的order_id = order_id、product_id = product_id是同一行内的字段自比较，判断结果永远为真，等价于直接查询全表，无法实现重复筛选。

注意：该需求不需要通过获取相邻下一行做值比对实现。相邻行比对的思路需要先按比对字段排序，不仅执行效率低，一旦重复组合的行没有相邻排列就会漏筛，稳定性很差。

写法1：窗口函数实现（推荐） #

支持MySQL8.0+、Hive、SparkSQL、PostgreSQL等所有兼容窗口函数的数据库引擎，逻辑清晰性能好：

SELECT order_item_id, order_id, product_id
FROM (
    SELECT
        *,
        COUNT(1) OVER (PARTITION BY order_id, product_id) AS combo_cnt
    FROM stg_83087_wc_order_product_lookup
) t
WHERE combo_cnt > 1;

核心逻辑是按order_id、product_id分区计数，每个组合的出现次数大于1即为重复组合，直接返回对应所有行即可。

写法2：分组关联实现（全引擎兼容） #

如果使用的数据库不支持窗口函数（比如MySQL5.x及更早版本），可以先分组查出所有重复的字段组合，再关联原表取数，兼容性最强：

SELECT a.*
FROM stg_83087_wc_order_product_lookup a
JOIN (
    SELECT order_id, product_id
    FROM stg_83087_wc_order_product_lookup
    GROUP BY order_id, product_id
    HAVING COUNT(1) > 1
) b 
ON a.order_id = b.order_id AND a.product_id = b.product_id;

两种写法返回结果完全一致，可根据实际使用的数据库环境选择。

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