消除组\u concat中的重复行

lc8prwob 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(423)

我有使用此sql的重复记录：

SELECT emp.id, first_name, second_name, last_name, department, positions,  
            GROUP_CONCAT(email, " ", email_type SEPARATOR " || ") AS mails,
            GROUP_CONCAT(number, " ", number_type SEPARATOR " || ") AS numbers
            FROM geography.employee AS emp
            INNER JOIN geography.employee_email AS ee ON emp.id = ee.email_FK
            LEFT JOIN geography.employee_number AS en ON emp.id = en.number_FK
            WHERE first_name = 'John' AND last_name = 'Ausini'
            GROUP BY emp.id

所以我得到这些邮件的结果：

heyhey@abv.bg personal || heyhey@abv.bg personal || summer_geals@bwtc.co work || summer_geals@bwtc.co work || ivanov@abv.bg personal || ivanov@abv.bg personal

对于数字：

+7654656656565 work || +7654656465655 personal || +7654656656565 work || +7654656465655 personal || +7654656656565 work || +7654656465655 personal

奇怪的是，在我的第二份记录中，邮件不是重复的，邮件：

hei@abv.bg personal || hurei@abv.bg personal || burei@abv.bg personal || work@bwtc.com work

……但在这张唱片里，我没有数字，也许这张唱片把它从最上面的那张中去掉了。我的预期输出不是每个记录重复数据（对于第一个记录示例）：

heyhey@abv.bg personal || summer_geals@bwtc.co work || ivanov@abv.bg personal

…数字也没有重复。distinct也不起作用（如果我把它放在select之后），我尝试过它，尽管我不喜欢掩盖问题。
我在表格中的数据：

sql mysql group-by group-concat

来源：https://stackoverflow.com/questions/51273329/eliminate-duplicate-rows-inside-group-concat

2条答案

按热度按时间

9jyewag01#

因为你只得到一个用户的信息，我同意萨尔曼a的回答；但是如果你是针对多个（或所有）用户，而用户往往有很多电子邮件和号码，这个版本可能会更快。

SELECT emp.id, first_name, second_name, last_name, department, positions, ee.mails, en.numbers
FROM geography.employee AS emp
INNER JOIN (SELECT email_FK, GROUP_CONCAT(email, " ", email_type SEPARATOR " || ") AS mails 
            FROM geography.employee_email 
            GROUP BY email_FK
) AS ee ON emp.id = ee.email_FK
LEFT JOIN (SELECT number_FK, GROUP_CONCAT(number, " ", number_type SEPARATOR " || ") AS numbers 
           FROM geography.employee_number 
           GROUP BY number_FK
) AS en ON emp.id = en.number_FK
;

赞(0）回复(0）举报 2021-06-21

ntjbwcob2#

mysql数据库 GROUP_CONCAT 允许您指定 DISTINCT 在连接之前应该消除重复项：

SELECT
    emp.id, first_name, second_name, last_name, department, positions,
    GROUP_CONCAT(DISTINCT email, " ", email_type SEPARATOR " || ") AS mails,
    GROUP_CONCAT(DISTINCT number, " ", number_type SEPARATOR " || ") AS numbers
FROM geography.employee AS emp
INNER JOIN geography.employee_email AS ee ON emp.id = ee.email_FK
LEFT JOIN geography.employee_number AS en ON emp.id = en.number_FK
WHERE first_name = 'John' AND last_name = 'Ausini'
GROUP BY emp.id

赞(0）回复(0）举报 2021-06-21

我来回答

消除组\u concat中的重复行

2条答案

相关问题

热门标签

最新问答