时间戳

首页 > 解决方案 > MySQL - 多个表上的 GROUP_CONCAT

mysql - MySQL - 多个表上的 GROUP_CONCAT

问题描述

我有这样的表和查询： SQLFIDDLE

SELECT P.*, 
       GROUP_CONCAT(`trait_name` SEPARATOR ', ') AS traits,
       GROUP_CONCAT(`filter_name` SEPARATOR ', ') AS filters
FROM `products` P 
NATURAL JOIN 
       `personality_traits` 
NATURAL JOIN 
       `product_to_traits` 
NATURAL JOIN 
       `filters` 
NATURAL JOIN 
       `product_to_filters` 
GROUP BY `product_name`

我想选择所有产品并显示给定产品所属的所有特征和过滤器（特征和过滤器在单独的表中）它几乎可以工作，结果正确但成倍增加。如果我在一个表上使用一个 GROUP_CONCAT，我的解决方案工作得非常好，当我添加另一个表时，结果会成倍增加。

安装：

    +------------+--------------+---------------+-----------------+
    | product_id | product_name | traits        | filters         |
    +------------+--------------+---------------+-----------------+
    | 1          | product1     | trait1,trait2 | filter5         |
    | 2          | product2     | trait2,trait3 | filter4,filter2 |
    | 3          | product3     | trait3,trait5 | filter3         |
    | 4          | product4     | trait5,trait1 | filter2         |
    | 5          | product5     | trait1        | filter1         |
    +------------+--------------+---------------+-----------------+

我得到：

    +------------+--------------+--------------------------------+------------------------------------+
    | product_id | product_name | traits                         | filters                            |
    +------------+--------------+--------------------------------+------------------------------------+
    | 1          | product1     | trait1,trait2                  | filter5,filter5                    |
    | 2          | product2     | trait2, trait2, trait3, trait3 | filter2, filter4, filter2, filter4 |
    | 3          | product3     | trait3,trait5                  | filter3,filter3                    |
    | 4          | product4     | trait5,trait1                  | filter2,filter2                    |
    | 5          | product5     | trait1                         | filter1                            |
    +------------+--------------+--------------------------------+------------------------------------+

谢谢！

标签： mysql

解决方案

尝试这个：

SELECT P.*, 
       GROUP_CONCAT( DISTINCT `trait_name` ORDER BY product_id SEPARATOR ',') AS traits,
       GROUP_CONCAT( DISTINCT `filter_name` ORDER BY product_id SEPARATOR ',') AS filters
FROM `products` P 
NATURAL JOIN 
       `personality_traits` 
NATURAL JOIN 
       `product_to_traits` 
NATURAL JOIN 
       `filters` 
NATURAL JOIN 
       `product_to_filters` 
GROUP BY `product_name`

推荐阅读