Mysql在使用GROUP_CONCAT的联接中重复的值 [英] MySql duplicated values in a join using GROUP_CONCAT

问题描述

比方说我有这个模式:

 _________                               _________
| Table 1 |                             | Table 2 |
|---------|          _______    ________|---------|
| id_a    |___      | link1 |   |       | id_b    |
| name    |   |     |-------|   |       | info    |
|_________|   |_____| id_a  |___|       | data    |
     |              | id_b  |           |_________|
     |              |_______|
     |
     |               _______
     |______________| link2 |_______
                    |-------|       |
                    | id_a  |       |   _________
                    | id_c  |       |__| Table 3 |
                    |_______|          |---------|
                                       | id_c    |
                                       | email   |
                                       |_________|

数据库中包含这些信息:

With these informations in the database :

表1

 _____________
| id_a | name |
|------|------|
|   1  | foo  |
|______|______|

表2

 _____________________
| id_b | info | data  |
|------|------|-------|
|   1  | bar  | corge |
|   2  | kux  | corge |
|   3  | fred | quux  |
|______|______|_______|

表3

 _________________
| id_c | email    |
|------|----------|
|   1  | [e-mail]  |
|   2  | [e-mail]  |
|   3  | [e-mail]  |
|   4  | [e-mail]  |
|   5  | [e-mail]  |
|______|__________|

链接1

 _____________
| id_a | id_b |
|------|------|
|   1  |   1  |
|   1  |   2  |
|   1  |   3  |
|______|______|

链接2

 _____________
| id_a | id_c |
|------|------|
|   1  |   1  |
|   1  |   2  |
|   1  |   3  |
|   1  |   4  |
|   1  |   5  |
|______|______|

当我尝试检索所需的数据时，我使用以下语法:

When I try to retrieve the datas that I want, I use this syntax:

SELECT t1.*,
       GROUP_CONCAT(DISTINCT t2.info SEPARATOR  '|') AS info,
       GROUP_CONCAT(DISTINCT t2.data SEPARATOR  '|') AS data,
       GROUP_CONCAT(DISTINCT t3.email SEPARATOR '|') AS email
FROM table1 AS t1
LEFT JOIN link1 AS l1
ON l1.id_a = t1.id_a
LEFT JOIN table2 AS t2
ON t2.id_b = l1.id_b
LEFT JOIN link2 AS l2
ON l2.id_a = t1.id_a
LEFT JOIN table3 AS t3
ON t3.id_c = l2.id_c
WHERE t1.id_a = ?
GROUP BY t1.id_a

问题是当我在列中有两个或多个相同值时(例如表2"/数据"的示例)，结果没有给出相同数量的串联值:

The problem is that when I have two or more same values in a column (example with "Table 2" / "data") the result dosn't give the same number of concatenated values :

array(
    'id_a'  => '1',
    'info'  => 'bar|kux|fred',
    'data'  => 'corge|quux',
    'email' => '[e-mail]|[e-mail]|[e-mail]|[e-mail]|[e-mail]'
)

由于我需要级联值完全相同的数字，以便以后在我的代码中将其用作单个输出(例如Bar Corge，Kux Corge，Fred Quux)，我想知道是否有可能像我在请求中直接期望的那样得到它们?

And as I need the concatenated values to be exactly the same number to use them later in my code as a single output (for example Bar Corge, Kux Corge, Fred Quux), I would like to know if it is possible to get them as I'm expecting directely in the request ?

推荐答案

所以我自己回答.我正在寻找的是CONCAT_WS.我的要求变成了

So I answer myself. What I was looking for is CONCAT_WS. My request become then

SELECT t1.*,
       GROUP_CONCAT(DISTINCT CONCAT_WS(
            ' ', t2.info, t2.data
       ) SEPARATOR  ', ') AS info,
       GROUP_CONCAT(DISTINCT t3.email SEPARATOR '|') AS email
FROM table1 AS t1
LEFT JOIN link1 AS l1
ON l1.id_a = t1.id_a
LEFT JOIN table2 AS t2
ON t2.id_b = l1.id_b
LEFT JOIN link2 AS l2
ON l2.id_a = t1.id_a
LEFT JOIN table3 AS t3
ON t3.id_c = l2.id_c
WHERE t1.id_a = ?
GROUP BY t1.id_a

这篇关于Mysql在使用GROUP_CONCAT的联接中重复的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！