mysql - GROUP_CONCAT with join 导致双记录

Question

我正在使用查询:

SELECT  `customers`.`customers_id`, 
            `customer_name`,
            GROUP_CONCAT(customer_tags.customer_tag_name ORDER BY customer_tag_name SEPARATOR ', '),
            ''
FROM `customers`
LEFT OUTER JOIN `customer_tags_ids` ON `customer`.`customers_id` = `customer_tags_ids`.`customers_id`
LEFT OUTER JOIN `customer_tags` ON `customer_tags_ids`.`customer_tags_ids_id` = `customer_tags`.`customer_tags_id`
WHERE `customers`.`account_id` = 1
GROUP BY `customers`.`customers_id`
ORDER BY `customers`.`customers_id` desc, `customers`.`customer_name` asc

关于下表

客户

+-------------+---------------+
|customers_id |  customer_name|
+-------------+---------------+
|1            | Customer 1    |
+-------------+---------------+
|2            | Customer 2    | 
+-------------+---------------+

customer_persons

+-----------+-------------+------------+------------+
| persons_id| customers_id| firstname  | lastname   |
+-----------+-------------+------------+------------+
|1          |  1          | Mehmet     | Yaman      |
+-----------+-------------+------------+------------+
|2          |  1          | Zafer      | Zorlu      |   
+-----------+-------------+------------+------------+
|3          |  2          | Serkan     | Eryaman    |
+-----------+-------------+------------+------------+
|4          |  2          | Nedim      | Yaman      |
+-----------+-------------+------------+------------+

客户标签

+-------------------+--------------------+
|customer_tags_id   |  customer_tag_name |
+-------------------+--------------------+
|1                  |  Google            |
+-------------------+--------------------+
|2                  |  Yahoo             |   
+-------------------+--------------------+
|3                  |  Aol               |
+-------------------+--------------------+
|4                  |  Facebook          |
+-------------------+--------------------+

customer_tags_ids

+--------------------+------------------+--------------+
|customer_tags_ids_id|  customer_tags_id| customers_id |
+--------------------+------------------+--------------+
|1                   |  1               | 1            |
+--------------------+------------------+--------------+
|2                   |  1               | 2            |   
+--------------------+------------------+--------------+
|3                   |  2               | 1            |
+--------------------+------------------+--------------+
|4                   |  2               | 2            |
+--------------------+------------------+--------------+
|5                   |  3               | 2            |
+--------------------+------------------+--------------+
|6                   |  4               | 2            |
+--------------------+------------------+--------------+

我需要得到结果:

+-----------+--------------+-----------------------------+----------------+
|customer_id|customer_name |firstname + lastname         | customer_tags  |   
+-----------+--------------+-----------------------------+----------------+
|1          | Customer 1   | Mehmet Yaman, Zafer Zorlu   | Google, Yahoo  |              
+-----------+--------------+-----------------------------+----------------+
|2          | Customer 2   | Serkan Eryaman, Nedim Yaman | Google, Yahoo, |
|           |              |                             | Aol, Facebook  |
+-----------+--------------+-----------------------------+----------------+

但是当我使用上面的查询时，我得到以下信息:

| CUSTOMERS_ID | CUSTOMER_NAME |                                                                                                       CUSTOMERNAME |                                               CUSTOMERTAGS |
|--------------|---------------|--------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------|
|            1 |    Customer 1 |                                                               Mehmet Yaman, Zafer Zorlu, Mehmet Yaman, Zafer Zorlu |                               Google, Google, Yahoo, Yahoo |
|            2 |    Customer 2 | Serkan Eryaman, Serkan Eryaman, Nedim Yaman, Nedim Yaman, Serkan Eryaman, Serkan Eryaman, Nedim Yaman, Nedim Yaman | Aol, Aol, Facebook, Facebook, Google, Google, Yahoo, Yahoo |

如您所见，客户名称和标签是重复的。我该如何解决这个问题？

Answer 1

要获得结果，您需要做一些事情。首先，您在 customer_tags 上的 JOIN 似乎没有使用正确的列。你有:

ON `customer_tags_ids`.`customer_tags_ids_id` = `customer_tags`.`customer_tags_id`

从您的数据看来您需要:

ON `customer_tags_ids`.`customer_tags_id` = `customer_tags`.`customer_tags_id`

然后因为你想在多列上使用GROUP_CONCAT，你需要在连接数据时使用DISTINCT:

SELECT  
  c.`customers_id`, 
  c.`customer_name`,
  GROUP_CONCAT(DISTINCT CONCAT(cp.`firstname`, ' ', cp.`lastname`) SEPARATOR ', ') as CustomerName,
  GROUP_CONCAT(DISTINCT ct.customer_tag_name ORDER BY ct.customer_tag_name SEPARATOR ', ') as CustomerTags
FROM `customers` c
INNER JOIN `customer_persons` cp
  ON c.`customers_id` = cp.`customers_id`
LEFT OUTER JOIN `customer_tags_ids` cti
  ON c.`customers_id` = cti.`customers_id`
LEFT OUTER JOIN `customer_tags` ct
  ON cti.`customer_tags_id` = ct.`customer_tags_id`
-- WHERE c.`customers_id` = 1
GROUP BY c.`customers_id`, c.`customer_name`
ORDER BY c.`customers_id`, c.`customer_name` asc;

参见 SQL Fiddle with Demo .这样得到结果:

| CUSTOMERS_ID | CUSTOMER_NAME |                CUSTOMERNAME |                 CUSTOMERTAGS |
|--------------|---------------|-----------------------------|------------------------------|
|            1 |    Customer 1 |   Mehmet Yaman, Zafer Zorlu |                Google, Yahoo |
|            2 |    Customer 2 | Serkan Eryaman, Nedim Yaman | Aol, Facebook, Google, Yahoo |