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

本文介绍了SQL多次将一个表连接到另一个表? (将产品映射到类别)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

假设我有一个Product，Category和Product_To_Category表.一个产品可以在多个类别中.

Let's say I have a Product, Category, and Product_To_Category table. A Product can be in multiple categories.

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

我想要一个SQL查询，它给我这样的结果

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 |

等

我能够做到这一点的最好方法是将一堆查询结合在一起.一个查询，用于查询给定产品的每个预期类别数.

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个查询.
• 第二，类别没有统一地放在同一列中.例如，有时某个产品可能带有食物，鲜花"，而另一个时候可能带有花朵，食品".

有人知道更好的方法吗?另外，此技术是否有技术名称?

Does anyone know of a better way to do this? Also, does this technique have a technical name?

推荐答案

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

I don't know what RDBMS you're using, but in MySQL you can use 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