SQL多次将一个表连接到另一个表？ (将产品映射到类别)

Question

假设我有一个 Product , Category , 和 Product_To_Category table 。一个产品可以属于多个类别。

    Product                     Category        Product_to_category    ID   |   NAME               ID  | Name      Prod_id | Cat_id    =====================       ============    ===================        1| Rose                    1| Flowers          1| 1        2| Chocolate Bar           2| Food             2| 2        3| Chocolate Flower                            3| 1                                                       3| 2

I would like an SQL query which gives me a result such as

    ProductName      | Category_1 | Category_2 | Category_3    =======================================================    Rose             | Flowers    |            |    Chocolate Flower | Flowers    | Food       |

etc.

The best way I've been able to get this is to union a bunch of queries together; one query for every expected number of categories for a given product.

select p.name, cat1.name, cat2.name
from
  product p, 
  (select * from category c, producttocategory pc where pc.category_id = c.id) cat1,
  (select * from category c, producttocategory pc where pc.category_id = c.id) cat2
where p.id = cat1.id 
  and p.id = cat2.id
  and cat1.id != cat2.id
union all
select p.name, cat1.name, null
from
  product p, 
  (select * from category c, producttocategory pc where pc.category_id = c.id) cat1
where p.id = cat1.id 
  and not exists (select 1 from producttocategory pc where pc.product_id = p.id and pc.category_id != cat1.id)

这有几个问题。

首先，我必须为每个预期类别重复这个联合；如果一个产品可以分为 8 个类别，我需要 8 个查询。

其次，类别没有统一放在同一列中。例如，有时产品可能有“食物，鲜花”，而另一种情况可能是“鲜花，食物”。

有谁知道更好的方法来做到这一点？另外，这种技术有技术名称吗？

Answer 1

我不知道您使用的是什么 RDBMS，但在 MySQL 中您可以使用 GROUP_CONCAT:

SELECT
  p.name,
  GROUP_CONCAT(c.name SEPARATOR ', ') AS categories
FROM
  product p
  JOIN product_to_category pc ON p.id = pc.product_id
  JOIN category c ON c.id = pc.category_id
GROUP BY
  p.name
ORDER BY
  p.name,
  c.name