mysql - 检索内连接中表的最后一条记录

Question

我有这三个表:

products
+----+--------+
| id | QRCode |
+----+--------+
|  1 | 1000   |
|  2 | 1001   |
+----+--------+

prices
+----+---------+------------+
| id | price   | product_id |
+----+---------+------------+
|  2 | $100001 |          1 |
|  3 | $100002 |          1 |
|  4 | $90001  |          2 |
|  5 | $90002  |          2 |
+----+---------+------------+

colors
+----+--------+-------------+
| id | color  | product_id  |
+----+--------+-------------+
|  1 | ffffff |           1 |
|  2 | f2f2f2 |           1 |
|  4 | aaaaaa |           2 |
|  5 | a3a3a3 |           2 |
+----+--------+-------------+

我想以返回的方式合并这三个:

• 根据product_id对颜色进行group_concat
• 检索每个分组价格的最后记录

这是所需的输出:

+--------+----------------+-------------+-------------+
| QRCode |    colors      |    price    | product_id  |
+--------+----------------+-------------+-------------+
|  1000  | ffffff, f2f2f2 |   $100002   |      1      |
|  1001  | aaaaaa, a3a3a3 |   $90002    |      2      |
+--------+----------------+-------------+-------------+

我尝试过的事情:

下面的查询返回每个分组价格的最后一条记录的product_id

SELECT product_id FROM price where id IN 
                                        (SELECT max(id) FROM price
                                         GROUP BY product_id)

然后我尝试将上面的查询作为子查询放入该查询中

SELECT products.QRCode, priceSubQ.price, GROUP_CONCAT(colors.color) as colors FROM products
INNER JOIN colors on colors.product_id = products.id
INNER JOIN ( /* I put query above here */ ) as priceSubQ ON priceSubQ.product_id = products.id
GROUP BY products.id

我做错了什么？

Answer 1

我遇到了这个link这帮助我理解了问题

将内部查询更改为:

SELECT product_id FROM ANY_VALUE(price) where id IN 
                                    (SELECT max(id) FROM price
                                     GROUP BY product_id) group by product_id

解决了我的问题。