mysql - 比较 mysql 中两个表的最低值

Question

Fiddle Example

有没有办法比较表 user_price 和 retailer_price 中的产品价格，找出哪些最低并与表 target_price< 中的目标价格相匹配？

这是我想要得到的结果:

   PRODUCT_ID   SELLER  Lowest_Price /* lower than target price */
   1            Amazon  90                      
   2            Paul    120

我正在使用 UNION ALL 来获得类似的结果，但我想直接比较来自 user_price 和 retailer_price 的价格，所以我不不需要有额外的 NULL 列。我知道如果用户价格和零售商价格存储在同一个表中会更容易，但在我的实际情况下，user_price 也有其他不同的值要存储，所以我必须使用表架构。谁能指出我正确的方向？

SELECT tp.product_id,up.user AS seller,
NULL AS merchant,NULL AS merchant_lowest_price,
up.price AS seller_lowest_price

FROM target_price tp  
INNER JOIN user_price up ON up.product_id = tp.product_id 
WHERE tp.target_price >= up.price

UNION ALL

SELECT tp.product_id,NULL AS seller,NULL AS seller_lowest_price,
rp.retailer,rp.price AS retailer_lowest_price
FROM target_price tp 
INNER JOIN retailer_price rp 
ON rp.product_id = tp.product_id
WHERE tp.target_price >= rp.price

示例表架构:

CREATE TABLE user_price
    (`product_id` int,`user` varchar(30),`price` int)
;

INSERT INTO user_price
    (`product_id`,`user`,`price`)
VALUES
    (1,'Tom',200),
    (1,'Sally',120),
    (2,'Peter',150),
    (2,'Paul',120)

;

CREATE TABLE retailer_price
    (`product_id` int,`retailer` varchar(30),`price` int)
;

INSERT INTO retailer_price
    (`product_id`,`retailer`,`price`)
VALUES
    (1,'Amazon',90),
    (2,'Target',400)

;

CREATE TABLE target_price
    (`product_id` int,`target_price` int)
;

INSERT INTO target_price
    (`product_id`,`target_price`)
VALUES
    (1,100),
    (2,130)

;

Answer 1

您可以合并零售商和用户价格表，并与目标价格表进行join。

如果有多个零售商或用户的价格低于目标价格，则将全部列出。如果您想要其中的最低价，那么您可以使用 group by 并获得最低价。

SQL Fiddle

SELECT tp.product_id,  up.seller , up.price as lowest_price
FROM target_price tp
join ( select price, retailer as seller, product_id
       from retailer_price
       union
       select price, user as seller,  product_id 
       from user_price
     ) up
on tp.product_id = up.product_id
and tp.target_price >= up.price