排序左JOIN选择查询

Question

我有两个表(product &products_price)我想对产品价格行进行排序，以便在左连接查询

中显示默认价格例如:product id = 14是两个价格项(num 31,25在products_price表中)

这个单一的查询结果，完美地显示是我想要的:

SELECT  id ,p_id ,qty,price,CEILING(price-(price*discount_percent / 100)) AS off_price, CASE qty=0 WHEN 0 THEN 1 ELSE 0 END as not_zero_qty 
FROM products_price
WHERE status=1 AND p_id=14
ORDER BY not_zero_qty DESC,off_price ASC

image: row id #31是第一个显示行但是当我把这个查询在左JOIN查询不工作，行id #25是第一行显示:(

my LEFT JOIN查询和结果是:图片:查询结果

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

Answer 1

您希望每种产品的价格最低。我看到您是外连接价格，所以有些产品在价格表中没有条目，而您仍然希望显示这些产品。您还考虑查询中的数量为0，因此您希望在另一行中只显示数量为0的价格，以防表中没有产品的非零数量条目。

对于这个，你有一个ORDER BY子句，你想用它来对每个产品的价格进行排名，并且只显示排名最好的价格(通常是高于零的最小价格)。

这通常是通过横向连接完成的，MySQL还不支持。在MySQL中，你可以使用ROW_NUMBER来代替你的行排序:

SELECT p.id, p.title, pp.price_id, pp.off_price
FROM products p
LEFT JOIN
(
SELECT  
id AS price_id,
p_id,
discount_percent,
qty,
price,
default_price,
CEILING(price - (price * discount_percent / 100)) AS off_price,
ROW_NUMBER() OVER (PARTITION BY p_id
ORDER BY (qty = 0),
CEILING(price - (price * discount_percent / 100))) AS rn
FROM products_price
WHERE status = 1 
) pp ON pp.p_id = p.id AND pp.rn = 1
WHERE p.status = 1
ORDER BY pp.off_price DESC;

说明:使用ROW_NUMBER，您可以根据所需的订单编号每个产品的价格行。然后，您只保留产品排名最好的价格行(rn = 1)。

Answer 2

当您将查询放入LEFT JOIN子查询中时，除非指定了LIMIT，否则SORT BY没有意义。试着把ORDER BY移到最外层，比如

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