Oracle中使用RANK()按多条件排序获取每个Item对应最小非零Qty的最低Class数据

原SQL错误原因 #

你原来的窗口函数使用了PARTITION BY ITEM, CLASS，会将同一个Item下不同Class的行拆分到独立分区单独排序，无法实现跨Class的优先级比对，所以结果不符合预期。

修正后的RANK()函数写法 #

只需要按Item分区，排序规则先按Qty升序，再按Class后缀的数字升序即可：

SELECT A.*, 
RANK() OVER(
PARTITION BY ITEM
ORDER BY QTY ASC, CAST(SUBSTR(Class, 6) AS UNSIGNED) ASC) AS item_rank
FROM fruits.info
WHERE QTY <> 0

不同SQL方言提取Class数字后缀的写法可以按需调整：

• PostgreSQL：SUBSTRING(Class FROM '(\d+)$')::INT
• SQL Server：CAST(RIGHT(Class, LEN(Class)-5) AS INT)

执行上述语句后你就能得到预期的排序结果，再嵌套一层子查询筛选item_rank = 1即可拿到最终结果。

更高性能的实现方案 #

如果数据量较大，可以用关联子查询替代窗口函数，Item字段有索引时性能提升尤为明显：

SELECT t1.*
FROM fruits.info t1
WHERE t1.QTY <> 0
AND NOT EXISTS (
    SELECT 1 FROM fruits.info t2
    WHERE t2.Item = t1.Item 
    AND t2.QTY <> 0
    AND (
        t2.QTY < t1.QTY 
        OR (t2.QTY = t1.QTY AND CAST(SUBSTR(t2.Class,6) AS UNSIGNED) < CAST(SUBSTR(t1.Class,6) AS UNSIGNED))
    )
)

该方案不需要全表排序，直接筛选符合条件的行，省去了中间排序的开销。

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