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

Question

出于某种原因，我使用以下查询从标签和人员表中获取重复值:

# 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”连接，重复项就会消失。我对这个问题有点困惑。

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

Answer 1

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

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