mysql - MySQL使用多个左连接获取重复项

由于某些原因，我使用以下查询从“标签和人”表中获取重复的值：

# Selects
SELECT
Organization.name AS 'Name',
GROUP_CONCAT(Person.name) AS 'Persons',
GROUP_CONCAT(Tag.name) AS 'Tags'

# From
FROM Organization

# Joins
LEFT JOIN Person ON Organization.id = Person.organizationID
LEFT JOIN Refs ON Organization.id = Refs.fromID
LEFT JOIN Tag ON Tag.id = Refs.toID

GROUP BY Organization.id

如果删除“ Person”或“ Refs + Tag” JOIN，则重复项消失。我对此问题有些困惑。

这是SQL Fiddle的链接：
http://sqlfiddle.com/#!9/6251f/3/0

最佳答案

标记/引用表的联接导致每个组织以及该组织内部的人员重复。您可以尝试在单独的子查询中汇总人员和标签。以下似乎有效：

SELECT
    org.name AS Name,
    t1.Persons,
    t2.Tags
FROM Organization org
LEFT JOIN
(
    SELECT organizationID, GROUP_CONCAT(name) AS Persons
    FROM Person
    GROUP BY organizationID
) t1
    ON org.id = t1.organizationID
LEFT JOIN
(
    SELECT r.fromID, GROUP_CONCAT(t.name) AS Tags
    FROM Refs r
    LEFT JOIN Tag t
        ON t.id = r.toID
    GROUP BY r.fromID
) t2
    ON org.id = t2.fromID;

Demo

关于mysql - MySQL使用多个左连接获取重复项，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49304610/