MySQL:如何使用子查询和连接选择 min()

Question

我有 3 个 MySQL 表。我已对它们进行了修剪，仅显示重要的列

items
id |  title                   |
 1 |  Hamlet                  |
 2 |  Romeo and Juliet        |
 3 |  The Merchant of Venice  |


listings
id | item_id |    condition     |
 1 |    1    |    Mint          |
 2 |    1    |    Used - Good   |
 3 |    2    |    New           |
 4 |    2    |    Mint          |
 5 |    2    |    Used - Poor   |
 6 |    3    |    Used - Poor   |
 7 |    3    |    Used - Good   |
 8 |    3    |    Used -  Good  |


itemListings_variations
id | listing_id |   price    
 1 |     1      |    20.00
 2 |     1      |    10.00
 3 |     1      |     5.00
 4 |     2      |     6.00
 5 |     2      |     5.50
 6 |     3      |     2.00
 7 |     4      |    12.00
 8 |     5      |     3.00
 9 |     6      |     9.00

如果例如我搜索罗密欧与朱丽叶，它应该返回:

item_id  |       title            |  min_price
   2     |   Romeo and Juliet     |     2.00

到目前为止，我有两个查询，它们需要通过子查询和联接进行组合。我有:

SELECT 
i.id AS item_id,
i.title 
FROM items i
WHERE  MATCH (i.title) AGAINST (:search)

还有:

SELECT 
l.item_id,
v.listing_id,
MIN(price) AS min_price
FROM itemListings_variation v, itemListings l
WHERE v.listing_id = l.id
GROUP BY v.listing_id

Answer 1

试试这个 -

SELECT i.id, i.title, MIN(price)
FROM items i
INNER JOIN listings l ON i.items = l.item_id
INNER JOIN itemListings_variations il ON l.id = il.listing_id
GROUP BY i.id, i.title

希望这有帮助。