我正在尝试从mysql中的三个数据表下面检索列，预期的输出是：
计数（orderdetails表中product_id存在的时间）
id（来自products表）
名称（来自products表）
订单表
+-----+--------+
| id | status |
+-----+--------+
| 124 | unpaid |
| 125 | paid |
| 126 | paid |
| 127 | paid |
| 128 | unpaid |
+-----+--------+
订单详情表
+-----+------------+----------+
| id | product_id | order_id |
+-----+------------+----------+
| 332 | 1 | 125 |
| 333 | NULL | 125 |
| 334 | 144 | 125 |
| 335 | 162 | 126 |
| 336 | 144 | 126 |
| 337 | 162 | 127 |
+-----+------------+----------+
产品表
+-----+------------------------------------------------------+
| id | name |
+-----+------------------------------------------------------+
| 1 | Chaacoca Argan Oil Daily Moisture Repair Conditioner |
| 144 | Sandalwood 8oz Hand and Body Lotion |
| 162 | Mayumi Squalane Skin Oil - 2.17 fl oz |
+-----+------------------------------------------------------+
预期的输出应如下所示：
+------------+----------+-----------------------------------------------------+
| product_id |count_prod|Product_name |
+------------+----------+-----------------------------------------------------+
| 1 | 1 |Chaacoca Argan Oil Daily Moisture Repair Conditioner |
| 144 | 2 |Sandalwood 8oz Hand and Body Lotion |
| 162 | 2 |Mayumi Squalane Skin Oil - 2.17 fl oz |
+------------+----------+-----------------------------------------------------+
任何建议的查询将有所帮助。
先感谢您）！
最佳答案
您可以像这样使用联接：
select a.*,count(b.order_id) as total_order,GROUP_CONCAT(c.id) as product_ids,GROUP_CONCAT(c.name) as product_names
from Orders a
left outer join orderdetails b on a.id = b.order_id
left outer join products c on b.product_id = c.id
group by a.id