SQL多次将一个表连接到另一个表? (将产品映射到类别) [英] SQL to join one table to another table multiple times? (Mapping products to categories)
问题描述
假设我有一个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
这篇关于SQL多次将一个表连接到另一个表? (将产品映射到类别)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!