LEFT JOIN关联products_price表排序取默认价格失效如何解决？

你原有写法失效的核心原因是：MySQL对无LIMIT的派生表内的ORDER BY会做优化忽略，外层GROUP BY按p.id分组时，会随机取匹配到的products_price表的第一条记录，不会遵循你子查询内的排序规则。此外你原写法中直接GROUP BY p.id但SELECT里有pp表的非聚合字段，在MySQL开启ONLY_FULL_GROUP_BY默认模式的情况下还会直接执行报错。

方案1：MySQL 8.0+ 窗口函数实现（推荐） #

用ROW_NUMBER()窗口函数按产品分组后按你的规则排序，取每组排序为1的记录即可，符合SQL标准，性能稳定：

SELECT p.id,p.title,pp.price_id,pp.off_price
FROM products p
LEFT JOIN (
    SELECT 
        id as price_id ,p_id ,
        CEILING(price-(price*discount_percent / 100)) AS off_price,
        -- 按产品分组，按你的规则排序生成序号
        ROW_NUMBER() OVER (PARTITION BY p_id ORDER BY CASE qty=0 WHEN 0 THEN 1 ELSE 0 END DESC, CEILING(price-(price*discount_percent / 100)) ASC) AS rn
    FROM products_price
    WHERE status=1 
) pp ON p.id=pp.p_id AND pp.rn = 1 -- 只取每个产品排序后的第一条价格
WHERE p.status=1
ORDER BY pp.off_price DESC

方案2：MySQL 5.x 低版本兼容实现 #

如果你的数据库不支持窗口函数，可以用带LIMIT的关联子查询实现，强制排序逻辑不会被优化器忽略：

SELECT p.id,p.title,pp.id AS price_id, CEILING(pp.price-(pp.price*pp.discount_percent / 100)) AS off_price
FROM products p
LEFT JOIN products_price pp ON pp.id = (
    SELECT id FROM products_price 
    WHERE p_id = p.id AND status=1 
    ORDER BY CASE qty=0 WHEN 0 THEN 1 ELSE 0 END DESC, CEILING(price-(price*discount_percent / 100)) ASC 
    LIMIT 1
)
WHERE p.status=1
ORDER BY off_price DESC

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