SQL中用RANK/ROWNUM筛选多产品订单时如何排除单产品组订单？

问题根因 #

你原SQL中RANK()的分区规则是ORDER_ID + PRODUCT_GROUP_ID，窗口统计范围被限制在同一个订单的同一个产品组内，无法跨产品组统计单个订单对应的总产品组数量，因此无法过滤仅含单个产品组的订单。

实现方案 #

方案1：窗口函数一次性统计（性能最优，支持需要保留全量明细的场景） #

在CTE中新增窗口统计字段，直接计算每个订单对应的不同产品组数量，过滤大于1的即可：

WITH TESTRANK AS(
    SELECT
        ORDER_ID
        ,PRODUCT_GROUP_ID
        ,PRODUCT_ID
        ,RANK() OVER(PARTITION BY ORDER_ID, PRODUCT_GROUP_ID ORDER BY PRODUCT_ID) AS ROWNUM_RANK
        -- 统计每个订单对应的不同产品组总数
        ,COUNT(DISTINCT PRODUCT_GROUP_ID) OVER(PARTITION BY ORDER_ID) AS GROUP_CNT
    FROM ORDER_DETAIL
    WHERE
        ORDER_ID IN (3574, 1417, 2265)
)
SELECT ORDER_ID, PRODUCT_GROUP_ID, PRODUCT_ID, ROWNUM_RANK 
FROM TESTRANK
-- 过滤仅含单个产品组的订单
WHERE GROUP_CNT > 1
-- 可按需保留你原有的行过滤逻辑
-- AND ROWNUM_RANK = 1

运行后会自动排除ORDER_ID=1417的所有记录，仅保留3574、2265两个符合要求的订单数据。

方案2：分组预筛+关联（兼容性更好，低版本SQL引擎也支持） #

如果你的数据库不支持窗口函数中使用DISTINCT，可以先分组筛选出符合要求的订单ID，再关联原表计算排名：

-- 第一步：筛选出含多个产品组的订单ID
WITH VALID_ORDERS AS(
    SELECT ORDER_ID
    FROM ORDER_DETAIL
    WHERE ORDER_ID IN (3574, 1417, 2265)
    GROUP BY ORDER_ID
    HAVING COUNT(DISTINCT PRODUCT_GROUP_ID) > 1
)
-- 第二步：关联原表计算产品排名
SELECT 
    o.ORDER_ID
    ,o.PRODUCT_GROUP_ID
    ,o.PRODUCT_ID
    ,RANK() OVER(PARTITION BY o.ORDER_ID, o.PRODUCT_GROUP_ID ORDER BY o.PRODUCT_ID) AS ROWNUM_RANK
FROM ORDER_DETAIL o
INNER JOIN VALID_ORDERS v 
    ON o.ORDER_ID = v.ORDER_ID

如果仅需要获取符合条件的订单ID列表，不需要明细和排名，直接使用VALID_ORDERS CTE的查询逻辑即可，性能最优。

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