mysql - 如何从 MySQL 获取产品变体

Question

如何从表中获取产品变体。如何为此应用 MySQL 查询

产品

+----+--------------+
| id | product_name |
+----+--------------+
|  1 | IPhone       |
+----+--------------+

product_variants

+----+------------+-----------------+
| id | product_id | product_variant |
+----+------------+-----------------+
|  1 |          1 | COLORS          |
|  2 |          1 | RAM             |
|  3 |          1 | STORAGE         |
+----+------------+-----------------+

product_variant_options

+----+------------+-----------------+
| id | variant_id | product_variant |
+----+------------+-----------------+
|  1 |          1 | BLUE            |
|  2 |          1 | GOLD            |
|  3 |          2 | 8GB             |
|  4 |          2 | 12GB            |
|  5 |          3 | 64GB            |
|  6 |          3 | 128GB           |
+----+------------+-----------------+

我想要这样的输出

输出

+----+------------+--------------+---------+------------+
| id | product_id | product_name | variant |  options   |
+----+------------+--------------+---------+------------+
|  1 |          1 | IPhone       | COLOR   | BLUE,GOLD  |
|  2 |          1 | IPhone       | RAM     | 8GB,12GB   |
|  3 |          1 | IPhone       | STORAGE | 64GB,128GB |
+----+------------+--------------+---------+------------+

标签： mysqlaggregate-functionsproduct-variations

Answer 1

我没有要尝试的数据库数据，但这应该可以

 SELECT 
    products.id, product_variants.product_id, products.product_name,
    product_variants.product_variant,
    (SELECT GROUP_CONCAT(product_variant) FROM product_variant_options AS vo WHERE vo.variant_id = product_variants.id) AS options
 FROM
    products
    LEFT JOIN product_variants ON products.id = product_variants.product_id

只需加入 products 和 product_variants 表和 GROUP_CONCAT 选项