MySql 连接具有不同行数的多个表

Question

我很抱歉这个可能令人困惑的标题，但我真的不知道如何很好地描述我的问题。我也对我的英语表示歉意，因为我不是母语人士。

假设我有这个架构:

 _________                               _________
| Table 1 |                             | Table 2 |
|---------|          ______     ________|---------|
| id_a    |___      | join |   |        | id_b    |
| name    |   |     |------|   |        | info    |
|_________|   |_____| id_a |___|        | data    |
                    | id_b |            |_________|
                    |______|

数据库中包含这些信息:

表 1:

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

表 2:

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

加入:

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

在我的数据库中，我还有其他与“连接”表类型相同的表。它们用于存储“表 1” ID 和其他表中的其他 ID(我在架构中只使用了一个连接表来简化问题)。

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

SELECT t1.*,
       t2.*
FROM table1 AS t1
LEFT JOIN join AS j
ON j.id_a = t1.id_a
LEFT JOIN table2 AS t2
ON t2.id_b = j.id_b
WHERE t1.id_a = ?

但由于结果是一个多行数组，而我期望只有一行，因此我尝试使用 GROUP_CONCAT。

所以我的语法变成了:

SELECT t1.*,
       GROUP_CONCAT(t2.info SEPARATOR '|') AS info,
       GROUP_CONCAT(t2.data SEPARATOR '|') AS data
FROM table1 AS t1
LEFT JOIN join AS j
ON j.id_a = t1.id_a
LEFT JOIN table2 AS t2
ON t2.id_b = j.id_b
WHERE t1.id_a = ?
GROUP BY t1.id_a

现在它工作正常并且返回一行。

现在的问题是，我将这个原则用于多个联接，并且每个联接返回与其他联接不同数量的多行。然后复制串联值，我可以获得如下结果:

arrray(
    [...]
    'info' => 'bar|bar|bar|bar|kux|kux|kux|kux|fred|fred|fred|fred|',
    'data' => 'baz|baz|baz|baz|corge|corge|corge|corge|corge|quux|quux|quux|quux'
)

所以我想知道是否有一种方法可以避免重复值并仍然在单个请求中检索我想要的数据？

Answer 1

根据您的数据，此查询应该返回您想要的内容:

SELECT t1.*,
       GROUP_CONCAT(t2.info SEPARATOR '|') AS info,
       GROUP_CONCAT(t2.data SEPARATOR '|') AS data
FROM table1 t1 LEFT JOIN
     join j
     ON j.id_a = t1.id_a LEFT JOIN
     table2 t2
     ON t2.id_b = j.id_b
WHERE t1.id_a = ?
GROUP BY t1.id_a;

如果需要，您可以使用DISTINCT删除重复项:

SELECT t1.*,
       GROUP_CONCAT(DISTINCT t2.info SEPARATOR '|') AS info,
       GROUP_CONCAT(DISTINCT t2.data SEPARATOR '|') AS data
FROM table1 t1 LEFT JOIN
     join j
     ON j.id_a = t1.id_a LEFT JOIN
     table2 t2
     ON t2.id_b = j.id_b
WHERE t1.id_a = ?
GROUP BY t1.id_a;

不过，通常最好修复查询，这样它就不会生成重复项。