php - 不重复重复获取MYSQL结果

Question

我很难从我的数据库中获取结果。我有一个按品牌列出汽车的表格，另一个表格按型号列出汽车，另一个表格指定哪个型号属于哪个品牌。

这是我的表格:

品牌:

+--------------+----------------+
| car_brand_id | car_brand_name |
+--------------+----------------+
| 1            | acura          |
+--------------+----------------+
| 2            | honda          |
+--------------+----------------+
| 3            | toyota         |
+--------------+----------------+
| 4            | audi           |
+--------------+----------------+

模型:

+--------------+----------------+
| car_model_id | car_model_name |
+--------------+----------------+
| 1            | sienna         |
+--------------+----------------+
| 2            | corolla        |
+--------------+----------------+
| 3            | mdx            |
+--------------+----------------+
| 4            | accord         |
+--------------+----------------+
| 5            | a4             |
+--------------+----------------+

作业:

+-----------+-----------------+-----------------+
| assign_id | car_brand_fk_id | car_model_fk_id |
+-----------+-----------------+-----------------+
| 1         | 1               | 3               |
+-----------+-----------------+-----------------+
| 2         | 2               | 4               |
+-----------+-----------------+-----------------+
| 3         | 3               | 1               |
+-----------+-----------------+-----------------+
| 4         | 3               | 2               |
+-----------+-----------------+-----------------+
| 5         | 4               | 5               |
+-----------+-----------------+-----------------+

目前我正在做以下事情:

SELECT brands.car_brand_id, brands.car_brand_name, models.car_model_name
FROM brands
LEFT JOIN assignments ON assignments.car_brand_fk_id=brands.car_brand_id 
LEFT JOIN models ON models.car_model_id=assignments.car_model_fk_id
ORDER BY brands.car_brand_name

这些是我的结果:

+--------------+----------------+----------------+
| car_brand_id | car_brand_name | car_model_name |
+--------------+----------------+----------------+
| 1            | acura          | mdx            |
+--------------+----------------+----------------+
| 2            | honda          | accord         |
+--------------+----------------+----------------+
| 3            | toyota         | sienna         |
+--------------+----------------+----------------+
| 3            | toyota         | corolla        |
+--------------+----------------+----------------+
| 4            | audi           | a4             |
+--------------+----------------+----------------+

如您所见，其中一些正在重复(丰田)。我想要的是以下内容:

+--------------+----------------+-----------------+
| car_brand_id | car_brand_name | car_model_name  |
+--------------+----------------+-----------------+
| 1            | acura          | mdx             |
+--------------+----------------+-----------------+
| 2            | honda          | accord          |
+--------------+----------------+-----------------+
| 3            | toyota         | sienna, corolla |
+--------------+----------------+-----------------+
| 4            | audi           | a4              |
+--------------+----------------+-----------------+

我该怎么做？

Answer 1

这可以通过分组和使用 group_concat 来完成:

SELECT brands.car_brand_id, brands.car_brand_name, group_concat(models.car_model_name separator ', ')
FROM brands
LEFT JOIN assignments ON assignments.car_brand_fk_id=brands.car_brand_id 
LEFT JOIN models ON models.car_model_id=assignments.car_model_fk_id
GROUP BY brands.car_brand_name
ORDER BY brands.car_brand_name