mysql - 将MySQL key 对转换为串联列表

我有3张桌子：

businesses ( id, name )
categories ( id, name )
business_categories ( business_id, category_id )

第三个表是多对多的，并且该对上有一个PRIMARY KEY。

我正在尝试创建一个类似于以下内容的视图：

business_name    category_names
Pizza Hut        Restaurants, Pizza, Fast Food
Chipotle         Restaurants, Mexican, Fast Food

这是我正在尝试的

SELECT `businesses`.`name`, CONCAT_WS( ',', (
    SELECT `name`
    FROM `categories`
    JOIN `business_categories`
        ON `categories`.`id` = `business_categories`.`category_id`
    WHERE `business_categories`.`business_id` = `businesses`.`id`
) )
FROM  `businesses`
ORDER BY  `businesses`.`id` ASC

但我收到此错误：

Subquery returns more than 1 row

最佳答案

您可以使用GROUP_CONCAT()函数来实现：

SELECT
  businesses.name,
  GROUP_CONCAT(categories.name) AS all_categories
FROM
  businesses
    LEFT JOIN business_categories
      ON businesses.id=business_categories.business_id
    LEFT JOIN categories
      ON business_categories.category_id=categories.id
GROUP BY
  businessess.name