如何使用SQL将一个表多次连接到另一个表？（将产品映射到类别）

Question

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

    Product                     Category        Product_to_category
    ID   |   NAME               ID  | Name      Prod_id | Cat_id
    =====================       ============    ===================
        1| 玫瑰                     1| 花卉              1| 1
        2| 巧克力棒                   2| 食品             2| 2
        3| 巧克力花                                 3| 1
                                                       3| 2

我想要一个SQL查询，使我得到以下结果：

    ProductName      | Category_1 | Category_2 | Category_3
    =======================================================
    玫瑰             | 花卉         |            |
    巧克力花         | 花卉         | 食品       |

等等。

我能够得到这个的最好方法是将一堆查询联合在一起；对于每个产品预期的类别数量进行一次查询。

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个查询。
• 其次，类别没有统一放置在同一列中。例如，有时一个产品可能有“食品、花卉”，而另一次则是“花卉、食品”。

有人知道更好的方法吗？此技术是否有技术名称？

- Mr. Shiny and New 安宇

Answer 1

我不知道你使用的是哪个关系型数据库管理系统，但在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

- Seb

---

没有 GROUP BY 子句，这个查询只会返回一行数据，就像 COUNT(*) 查询一样只会返回一行数据。 - chim

Answer 2

SELECT p.name, cat_food.name, cat_flowers.name
FROM
  product p
  left outer join  Product_to_category pc_food 
    on p.id = pc_food.Prod_id
  left outer join Category cat_food
    on pc_food.Cat_id = cat_food.id
    AND cat_food.name = 'Food'
  left outer join  Product_to_category pc_flowers
    on p.id = pc_flowers.Prod_id
  left outer join Category cat_flowers
    on pc_flowers.Cat_id = cat_flowers.id
    AND cat_flowers.Name = 'Flowers'

只有在您知道可能类别的数量并将它们放入列中时，它才能正常工作。这就是（标准）SQL的工作方式，列数不是动态的。

- Stefan Steinegger

Answer 3

Seb的答案让我找到了一个解决方法。我正在使用Oracle，它有一些函数可以模仿MYSQL的group_concat。这里是一个例子。这不会生成列，因此并不像纯SQL解决方案那样好，但对于我的当前目的来说是合适的。

with data as
( 
  select 
    pc.id cat,
    p.id prod, 
    row_number() over( partition by p.id order by pc.id) rn,
    count(*) over (partition by p.id) cnt
  from product_to_category pc, product p
  where pc.product_id = p.id
)
select prod, ltrim(sys_connect_by_path(cat, ','), ',') cats
  from data
 where rn = cnt
 start with rn = 1 connect by prior prod = prod and prior rn = rn - 1
 order by prod

这将生成如下数据

产品 | 目录
===========
284  |   12
285  |   12
286  | 9,12

我可以根据需要编辑ltrim(sys_connect_by_path())列以生成所需的任何数据。

- Mr. Shiny and New 安宇

Answer 4

你无法通过严格的SQL查询创建这些结果。你试图要生成的是所谓的数据透视表。许多报表工具都支持这种行为，你可以选择产品和类别，然后将类别转换为透视列。

我相信SQL Server Analysis Services也支持这样的功能，但我没有使用SSAS的经验。

- Adam Robinson