从三个表中选择两个order by前两个group by

Question

我想要一份最新和最低报价的产品清单

表产品

:

id | name

表提供

:

id | product_id | price | created | dealer_id

表——

:

id | offer_id | status

I have try:

SELECT * FROM product INNER JOIN 
    (
        SELECT offer.product_id , offer.price
        FROM offer 
        LEFT JOIN invalids 
        ON offer.id = invalids.offer_id 
        WHERE invalids.id IS NULL 
        GROUP BY offer.dealer_id
        ORDER BY offer.created DESC
    ) o 
ON o.product_id = product.id 
ORDER BY product.name

我试过sqlfiddle http://sqlfiddle.com/#!带有此报价值的9/32658/3:

(`id`, `price`, `dealer_id`, `product_id`, `created`)  
(1,12.60,1,1,'2015-05-17 08:44:45'),  
(2,13.00,1,1,'2015-08-17 08:44:45'),  
(3,20.00,1,1,'2015-08-17 08:45:30'),  
(4,10.00,1,1,'2015-08-17 08:45:46'),  
(5,4.00,2,1,'2015-05-17 08:44:11'),  
(6,11.00,2,1,'2015-08-17 08:44:46'),  
(7,5.00,2,1,'2015-08-17 08:45:31'),  
(9,110.00,2,2,'2015-08-17 08:46:58'),  
(10,11.00,2,2,'2015-08-17 08:47:12');  

产品ID 1的期望值是报价ID 7，价格5。

这些步骤我想我必须实现:

1. created订购报价，dealer_id分组获取最新条目
2. 从步骤1中获取结果并按价格排序以获得最小价格。
3. 所有产品

也许我必须使用第二个SELECT FROM offer与GROUP BY和ORDER BY，但我如何从第一个(外部)选择得到product_id ?

Answer 1

我将从获取每个产品报价的最新日期开始，像这样:

SELECT product_id, MAX(created) AS latestOffer
FROM offer
GROUP BY product_id;

一旦你有了这个，你可以将它连接到原来的表来获得那个报价:

SELECT o.*
FROM offer o
JOIN(
   SELECT product_id, MAX(created) AS latestOffer
   FROM offer
   GROUP BY product_id) tmp ON tmp.product_id = o.product_id AND tmp.latestOffer = o.created;

下面是一个SQL Fiddle的例子。

Answer 2

这个查询应该对您有所帮助:

SELECT * 
FROM product
JOIN (
  SELECT product_id, min(price) as minPrice, max(created) as newestOffer 
  FROM offer
  WHERE id NOT IN (SELECT offer_id FROM invalids)
  GROUP BY 1
) as b
ON product.id = b.product_id

Answer 3

根据我对你的理解，这是在黑暗中拍摄的…

大量嵌套子查询…继续想吧，一定有更好的办法……

SELECT OO.ID, OO.Price, OO.Dealer_Id, OO.Product_ID, OO.created, P.name 
FROM Offer OO
INNER JOIN (
  SELECT Min(Price) as MinP
  FROM offer O
  INNER JOIN (
    SELECT max(OI.created) as LatestOffer, OI.Dealer_ID, OI.Product_ID 
    FROM  Offer OI
    LEFT JOIN invalids I
     on OI.Id = I.offer_Id
    WHERE I.ID is null
    GROUP BY OI.Dealer_Id, OI.Product_Id
   ) B
   on O.Dealer_Id = B.Dealer_Id
  and O.Product_Id = B.Product_Id
  and O.Created = B.LatestOffer
  ) Z
 on OO.Price = Z.MinP
INNER JOIN product P 
 on P.ID = OO.Product_ID

SQL小提琴