mysql - SQL - 当多个左连接时重复标签返回

Question

我正在获取一个预订，其中有多个链接标签和附加到该预订的客户。

有两个中间表用于跟踪其他表之间的关联:tag_assocs 在 bookings(在本例中)和 tags 表之间, assocs 在 bookings 和 customers 表之间。

SQL(简化):

SELECT
    bookings.id,
    GROUP_CONCAT(tag SEPARATOR "|") AS tags,
    GROUP_CONCAT(customers.id SEPARATOR "|") AS customer_id
FROM bookings
LEFT JOIN tag_assocs ON tag_assocs.item_id = bookings.id && tag_assocs.item_type = "bookings"
LEFT JOIN tags ON tags.id = tag_assocs.tag_id
LEFT JOIN assocs ON assocs.item_id = bookings.id && assocs.item_type = "bookings.customer_id"
LEFT JOIN customers ON customers.id = assocs.foreign_item_id
WHERE bookings.id = "a64l_ku-4af"
GROUP BY bookings.id
ORDER BY bookings.modified desc
LIMIT 20

现在，有问题的预订 (a64l_ku-4af) 只有一个标签 - 在数据库中得到确认。也就是说，对于标签some-tag，它在tag_assocs中只有一个条目。实际上，这是由唯一索引阻塞重复强制执行的。但它会回来两次，即

--------------------
| tags             |
--------------------
| some-tag|some-tag|
--------------------

两部分问题:

1)这是为什么？

2) 我可以通过将 GROUP_CONCAT(tag ... 更改为 GROUP_CONCAT(DISTINCT tag ...) 来解决这个问题，但我觉得这是一种 hack，并没有' 解决问题的原因。

Answer 1

让我们用一个例子来解释。假设我们有以下表格:

第 1 部分:原始查询

table 上书籍:

id | book
------------
 1 | Book 1
 2 | Book 2

表格标签:

id | tag
-----------
 1 | Hobby
 2 | Sport
 3 | Work

表格 books2tags(用于使用适当的标签标记书籍):

book_id | tag_id
----------------
      1 |     1
      1 |     2
      2 |     2
      2 |     3

现在两本书都附上了两个标签和结果

SELECT b.book, t.tag FROM books b
INNER JOIN books2tags bt ON b.id = bt.book_id
INNER JOIN tags t ON bt.tag_id = t.id

就是这样(顺序无关紧要):

book   | tag
---------------
book 1 | Hobby
book 1 | Sport
book 2 | Sport
book 2 | Work

要获取包含每本书标签列表的字符串，我们使用

SELECT b.book, GROUP_CONCAT(t.tag SEPARATOR '|') as tags FROM books b
INNER JOIN books2tags bt ON b.id = bt.book_id
INNER JOIN tags t ON bt.tag_id = t.id
GROUP BY b.book

结果可能是:

book   | tags
---------------------
book 1 | Hobby|Sport
book 2 | Work|Sport

第 2 部分:加入另一个表

如果我们有另一个包含可选附加信息的表，让我们举个例子:

id | book_id | example
-------------------------------------
 1 |      1  | 'example information'
 2 |      2  | 'another example'

我们通过 LEFT JOIN 获得此附加信息:

SELECT b.book, t.tag, e.example FROM books b
INNER JOIN books2tags bt ON b.id = bt.book_id
INNER JOIN tags t ON bt.tag_id = t.id
LEFT JOIN example e ON b.id = e.book_id

结果将是:

book   | tag     | example
----------------------------
book 1 | Hobby   | example information
book 1 | Hobby   | another example
book 1 | Sport   | example information
book 1 | Sport   | another example
book 2 | Sport   | NULL
book 2 | Work    | NULL

您会看到，原始查询中第 1 本书的每一行出现在结果中的频率与我们新连接的表示例中匹配行的频率一样。如果我们使用我们的旧查询来按书获取标签，但是通过添加左连接我们将获得

SELECT b.book, GROUP_CONCAT(t.tag SEPARATOR '|') as tags FROM books b
INNER JOIN books2tags bt ON b.id = bt.book_id
INNER JOIN tags t ON bt.tag_id = t.id
LEFT JOIN example e ON b.id = e.book_id
GROUP BY b.book

book   | tags
---------------------
book 1 | Hobby|Hobby|Sport|Sport
book 2 | Work|Sport

那是因为现在有两行带有标签“Hobby”和两行带有标签“Sport”。但是为了获得标签列表，我们使用 DISTINCT 得到了正确的结果。这不是黑客:

SELECT b.book, GROUP_CONCAT(DISTINCT t.tag SEPARATOR '|') as tags FROM books b
INNER JOIN books2tags bt ON b.id = bt.book_id
INNER JOIN tags t ON bt.tag_id = t.id
LEFT JOIN example e ON b.id = e.book_id
GROUP BY b.book

book   | tags
---------------------
book 1 | Hobby|Sport
book 2 | Work|Sport

结论

我想我已经通过加入另一个表作为问题第 1 部分的答案证明了为什么标签会被复制，并且可以向您保证您的解决方案不是 hack，而是这个问题的正确解决方案。

看看 this demo 也是。