我有这三个表：
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
我究竟做错了什么？
最佳答案
像下面这样的东西应该起作用（未经测试）。
SELECT
products.QRCode,
priceSubQ.price,
GROUP_CONCAT(colors.color) as colors
FROM
products
LEFT JOIN colors
ON colors.product_id = products.id
LEFT JOIN (
SELECT
MAX(p1.id) as p1maxId,
p2.price AS price,
p2.product_id AS product_id
FROM
prices p1
INNER JOIN prices p2
ON p1.p1maxId = p2.id
GROUP BY
p1.product_id
) AS priceSubQ
ON priceSubQ.product_id = products.id
GROUP BY
products.id
关于mysql - 检索内部联接中表的最后一条记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/46590326/