MySQL查询实现优先取有库存的商品最低价，无库存则取全局最低价

可以通过窗口函数的自定义排序规则实现，兼容MySQL 8.0+、PostgreSQL、Oracle、Spark SQL等所有支持标准SQL窗口函数的数据库。

核心实现思路 #

给每个商品的所有记录按规则做优先级排序，直接取每个商品排序第一的记录即可：

1. 第一排序规则：库存大于0的记录优先级高于库存为0的记录
2. 第二排序规则：同优先级的记录按价格升序排列，价格越低排越靠前

实现代码 #

WITH ranked_goods AS (
    SELECT
        product_id,
        warehouse,
        quantity,
        price,
        ROW_NUMBER() OVER (
            PARTITION BY product_id
            ORDER BY
                CASE WHEN quantity > 0 THEN 1 ELSE 2 END, -- 库存>0的记录优先排序
                price ASC -- 同优先级价格越低越靠前
        ) AS rank_num
    FROM 你的表名 -- 替换为实际表名
)
SELECT product_id, warehouse, quantity, price
FROM ranked_goods
WHERE rank_num = 1;

逻辑验证 #

和你提供的示例数据匹配的排序结果如下：

• product_id为1的商品：存在库存>0的记录，A仓库库存5价格14是库存>0的记录中价格最低的，排序第一，符合预期
• product_id为2的商品：所有记录库存都是0，直接取所有记录中价格最低的C仓库记录，符合预期
• product_id为3的商品：只有一条符合条件的记录，直接返回，符合预期
  如果同一个商品有多条记录同时满足「同优先级+同最低价格」需要全部返回的话，把代码中的ROW_NUMBER()替换为RANK()即可。

老版本MySQL（5.x及以下不支持窗口函数）兼容方案 #

SELECT t.*
FROM 你的表名 t
INNER JOIN (
    SELECT
        product_id,
        CASE
            WHEN MIN(CASE WHEN quantity>0 THEN price END) IS NOT NULL
            THEN MIN(CASE WHEN quantity>0 THEN price END)
            ELSE MIN(price)
        END AS target_price,
        CASE WHEN MIN(CASE WHEN quantity>0 THEN price END) IS NOT NULL THEN 1 ELSE 0 END AS has_valid_stock
    FROM 你的表名
    GROUP BY product_id
) m ON t.product_id = m.product_id
AND (
    (m.has_valid_stock = 1 AND t.quantity>0 AND t.price = m.target_price)
    OR (m.has_valid_stock = 0 AND t.price = m.target_price)
)
-- 同商品有多条符合条件记录只取一条的话保留下面这句，否则删除
GROUP BY t.product_id;

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