指定要在JOIN mysql中选择的正确元素

Question

我有三张桌子：

---------------------
|      PRODUCTS     |
---------------------
| id |    product   |  
---------------------
| 1  | super prod 1 |
| 2  | super prod 2 |
| 3  | super prod 3 |
| 4  | super prod 4 |
| 5  | super prod 5 |
---------------------

--------------------------
|         images         |
--------------------------
| id |p_id|      image   |  
--------------------------
| 1  | 1  |  myimg01.jpg |
| 2  | 1  |  myimg02.jpg |
| 3  | 1  |  myimg03.jpg |
| 4  | 2  |  myimg04.jpg |
| 5  | 2  |  myimg05.jpg |
| 6  | 3  |  myimg06.jpg |
| 7  | 3  |  myimg07.jpg |
| 8  | 4  |  myimg08.jpg |
| 9  | 4  |  myimg09.jpg |
| 10 | 5  |  myimg10.jpg |
--------------------------


--------------------------
|          SALES         |
--------------------------
| id |p_id|      sale    |  
--------------------------
| 1  | 1  |      45      |
| 1  | 2  |      29      |
| 1  | 3  |      12      |
| 1  | 4  |      55      |
| 1  | 5  |      16      |
--------------------------

如果我使用以下方法查询所有表：

SELECT products.id,products.product, images.image, sales.sale 
FROM products 
LEFT JOIN images ON images.p_id = products.id 
LEFT JOIN sales ON sales.p_id = product.id 
GROUP BY product.id

我有以下结果：

---------------------------------------------------
| id |    product   |     image    |      sale    | 
---------------------------------------------------
| 1  | super prod 1 |  myimg03.jpg |      45      |
| 2  | super prod 2 |  myimg05.jpg |      29      |
| 3  | super prod 3 |  myimg07.jpg |      12      |
| 4  | super prod 4 |  myimg09.jpg |      55      |
| 5  | super prod 5 |  myimg10.jpg |      16      |
---------------------------------------------------

请注意:在“图像”栏中，我有一个产品的最后一个图像。我的怀疑从这里开始。

如何使用单个查询(如果可以:一个简单的查询)获得分配给产品的第一个映像？如果你有这方面的想法或线索的话，非常感谢。

警察:对不起，穆英语不好.

Answer 1

您的查询使用分组，但在任何列上都没有发生任何聚合。默认情况下，我相信MySQL将允许您这样做，但是它不受支持，或者被普遍认为是安全的，因为MySQL会为您的非聚合列选择任意值。(见：https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_仅限_完整_组_通过)

因此，假设您想要正确地分组，我猜您可能想要每个产品的销售总额，并返回到这个聚合，您想要“第一个”的形象。

您使用的是什么版本的MySQL？如果您使用的是< v8.0，那么就没有“简单”的方法可以有效地做到这一点，除非可以做一些假设。MySQL < v8.0缺少窗口函数/ groupwise排序(google "row_number over“)，而且我在8.0中还没有使用该功能，所以我无法对此进行讨论。

但是，如果您能够假设一些事情，您可能会得到一个有效的查询。

假设您的图像表ID是auto_increment，并且您总是希望在此查询中返回ID最低的图像，那么下面的内容应该可以工作：

mysql> select p.id, p.product, i.image, sum(sale) from products p left join (select p_id, min(id) as minImageId from images group by p_id) as minI on p.id = minI.p_id left join images i on minI.minImageId = i.id left join sales s on p.id = s.p_id group by p.id;
+----+--------------+-------------+-----------+
| id | product      | image       | sum(sale) |
+----+--------------+-------------+-----------+
|  1 | super prod 1 | myimg01.jpg |        45 |
|  2 | super prod 2 | myimg04.jpg |        29 |
|  3 | super prod 3 | myimg06.jpg |        12 |
|  4 | super prod 4 | myimg08.jpg |        55 |
|  5 | super prod 5 | myimg10.jpg |        16 |
+----+--------------+-------------+-----------+
5 rows in set (0.00 sec)

下面是我如何从products表到完成的查询构建查询：

SELECT *
FROM products;
+----+--------------+
| id | product      |
+----+--------------+
|  1 | super prod 1 |
|  2 | super prod 2 |
|  3 | super prod 3 |
|  4 | super prod 4 |
|  5 | super prod 5 |
+----+--------------+


-- for each product, get the minimum image ID
SELECT *
FROM products
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON products.id = minI.p_id;
+----+--------------+------+------------+
| id | product      | p_id | minImageId |
+----+--------------+------+------------+
|  1 | super prod 1 |    1 |          1 |
|  2 | super prod 2 |    2 |          4 |
|  3 | super prod 3 |    3 |          6 |
|  4 | super prod 4 |    4 |          8 |
|  5 | super prod 5 |    5 |         10 |
+----+--------------+------+------------+


-- clean up query by removing a column we do not care to see
SELECT p.id,
       p.product,
       minI.minImageId
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id;
+----+--------------+------------+
| id | product      | minImageId |
+----+--------------+------------+
|  1 | super prod 1 |          1 |
|  2 | super prod 2 |          4 |
|  3 | super prod 3 |          6 |
|  4 | super prod 4 |          8 |
|  5 | super prod 5 |         10 |
+----+--------------+------------+

-- join in image table using the min image ID we found ealier
SELECT p.id,
       p.product,
       minI.minImageId,
       i.image
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id
LEFT JOIN images i ON minI.minImageId = i.id;
+----+--------------+------------+-------------+
| id | product      | minImageId | image       |
+----+--------------+------------+-------------+
|  1 | super prod 1 |          1 | myimg01.jpg |
|  2 | super prod 2 |          4 | myimg04.jpg |
|  3 | super prod 3 |          6 | myimg06.jpg |
|  4 | super prod 4 |          8 | myimg08.jpg |
|  5 | super prod 5 |         10 | myimg10.jpg |
+----+--------------+------------+-------------+

-- bring in and aggregate our sales data. if you do not put an aggregate function on the sale value, mysql will pick a value for you arbitrarily
SELECT p.id,
       p.product,
       minI.minImageId,
       i.image,
       sum(sale)
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id
LEFT JOIN images i ON minI.minImageId = i.id
LEFT JOIN sales s ON p.id = s.p_id
GROUP BY p.id;
+----+--------------+------------+-------------+-----------+
| id | product      | minImageId | image       | sum(sale) |
+----+--------------+------------+-------------+-----------+
|  1 | super prod 1 |          1 | myimg01.jpg |        45 |
|  2 | super prod 2 |          4 | myimg04.jpg |        29 |
|  3 | super prod 3 |          6 | myimg06.jpg |        12 |
|  4 | super prod 4 |          8 | myimg08.jpg |        55 |
|  5 | super prod 5 |         10 | myimg10.jpg |        16 |
+----+--------------+------------+-------------+-----------+


-- clean up query by removing a column we do not care about
SELECT p.id,
       p.product,
       i.image,
       sum(sale)
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id
LEFT JOIN images i ON minI.minImageId = i.id
LEFT JOIN sales s ON p.id = s.p_id
GROUP BY p.id;
+----+--------------+-------------+-----------+
| id | product      | image       | sum(sale) |
+----+--------------+-------------+-----------+
|  1 | super prod 1 | myimg01.jpg |        45 |
|  2 | super prod 2 | myimg04.jpg |        29 |
|  3 | super prod 3 | myimg06.jpg |        12 |
|  4 | super prod 4 | myimg08.jpg |        55 |
|  5 | super prod 5 | myimg10.jpg |        16 |
+----+--------------+-------------+-----------+