html - 让两个(最少)在 mysql 的一个查询中工作

Question

Fiddle Example

我想检索每种产品最便宜的二手价格和新价格。这是我想要得到的结果:

NAME    NEW_PRICE   NEW_MERCHANT    OLD_PRICE   OLD_MERCHANT
A852    1000        Amazon          200         BestBuy
T12h    NULL        NULL            300         BestBuy

表架构

CREATE TABLE price
    (`product_id` int,`price` int,`conditions` varchar(30),`merchant` varchar(30))
;

INSERT INTO price
    (`product_id`,`price`,`conditions`,`merchant`)
VALUES
    (1,1000,'New','Amazon'),
    (1,200,'Old','BestBuy'),
    (1,1500,'New','Target'),
    (1,300,'Old','Amazon'),
    (2,600,'Old','Target'),
    (2,300,'Old','BestBuy')

;

CREATE TABLE product
    (`product_id` int,`name` varchar(30))
;

INSERT INTO product
    (`product_id`,`name`)
VALUES
    (1,'A852'),
    (2,'T12h')

;

以下查询只能获取上述结果的第一行。由于没有新价格，因此无法获取 T12h 的记录。而且我还觉得应该有更好的方法，而不是在 FROM 子句 中重复子查询来匹配每个产品条件的价格和商家。有人能指出我正确的方向吗？

SELECT a.name,a.New_price,a.New_merchant,
c.Old_price,c.Old_merchant
FROM
 (
  SELECT
   p.name,pr.product_id,MIN(pr.price) AS New_price,
   pr.merchant AS New_merchant
   FROM price pr
   INNER JOIN product p ON p.product_id = pr.product_id
   WHERE pr.conditions = 'NEW'
 )a 
 LEFT JOIN
 (
  SELECT product_id,MIN(price) AS new_price
    FROM price WHERE conditions = 'New'
 )b 
  ON a.new_price = b.new_price 
  AND a.product_id = b.product_id

 LEFT JOIN
  (
  SELECT
   p.name,pr.product_id,MIN(pr.price) AS Old_price,
   pr.merchant AS Old_merchant
   FROM price pr
   INNER JOIN product p ON p.product_id = pr.product_id
   WHERE pr.conditions = 'Old'
 )c ON c.product_id = a.product_id
  LEFT JOIN
 (
  SELECT product_id,MIN(price) AS Old_price
    FROM price WHERE conditions = 'Old'
 )d 
 ON c.Old_price = d.Old_price 
 AND c.product_id = d.product_id

Answer 1

我认为这就是您所看到的，首先使用New获取最低价格，使用Old获取最低价格，然后left join它们与产品

select
pr.*,
p1.price as new_price,
p1.merchant as new_merchant,
p2.price as old_price,
p2.merchant as old_merchant
from product pr
left join (
  select t1.* from price t1
  left join price t2 on t2.product_id = t1.product_id
  and t2.price < t1.price
  and t2.conditions = 'New'
  and t1.conditions = t2.conditions
  where t2.product_id is null and t1.conditions = 'New'
)p1
on p1.product_id = pr.product_id
left join
(
  select t1.* from price t1
  left join price t2 on t2.product_id = t1.product_id
  and t2.price < t1.price
  and t2.conditions = 'Old'
  and t1.conditions = t2.conditions
  where t2.product_id is null and t1.conditions = 'Old'
)p2
on p2.product_id = pr.product_id
;

<强> DEMO