mysql - 复杂(ish)连接问题:

Question

我在 mySQL 数据库中有下表(注意:这些是总结的，因此它们与这个问题相关，一些列已被省略)。

author (id, username, password etc.)
thread (id, title, content)
tag (id, name)
reply (id, content)
thread_replies (thread_id, reply_id)
author_replies (author_id, reply_id)
thread_tags (thread_id, tag_id)
author_threads (author_id, thread_id)

现在要获取某个作者的帖子，我通常这样做:

SELECT thread.title, thread.id AS thread_id, thread.content, author.username, author.id AS author_id
FROM thread
JOIN author_threads ON thread.id = author_threads.thread_id
JOIN author ON author_threads.author_id = author.id
WHERE author.id = '12'

这工作正常，但是当我尝试获取与这些线程关联的标签时:

SELECT thread.title, thread.id AS thread_id, thread.content, author.username, author.id AS author_id, GROUP_CONCAT( DISTINCT tag.name
ORDER BY tag.name DESC
SEPARATOR ',' ) AS tags
FROM thread
JOIN thread_tags ON thread.id = thread_tags.thread_id
JOIN tag ON thread_tags.tag_id = tag.id
JOIN author_threads ON thread.id = author_threads.thread_id
JOIN author ON author_threads.author_id = author.id
WHERE author.id = '12'
LIMIT 0 , 30

它仅在一列中显示第一个线程以及与该作者关联的所有标签。

我在这里做错了什么？

另外，如果我想计算作者为每个主题获得的回复数量，该怎么做？

Answer 1

由于您在查询中使用聚合 (GROUP_CONCAT)，因此您的查询将被分组。由于您没有 group by 子句，因此您的组是整个结果集(因此可以看到作者使用的每个标签)。由于 MySQL 允许在分组语句中使用非分组列，因此您不会收到错误，但不会获得所需的查询。

为了检索正确的结果，您需要根据 thread.id 对查询进行分组。

select
    thread.title, 
    thread.id as thread_id, 
    thread.content, 
    author.username, 
    author.id as author_id, 
    group_concat(distinct tag.name order by tag.name desc separator ',') as tags

from thread

join thread_tags ON thread.id = thread_tags.thread_id
join tag ON thread_tags.tag_id = tag.id
join author_threads ON thread.id = author_threads.thread_id
join author ON author_threads.author_id = author.id

where author.id = '12'

group by thread.id

limit 0 , 30

这应该在 MySQL 中工作，尽管它不是符合 ANSI 的 SQL，因为您在 select 子句中使用非分组列而不进行任何聚合。您可以保持原样，也可以编写更兼容的 SQL，并在除 thread.id 之外的所有列上使用诸如 max 之类的内容。这看起来不太漂亮，但它是合规的。

SELECT 
    max(thread.title) as title, 
    thread.id as thread_id, 
    max(thread.content) as content, 
    max(author.username) as username, 
    max(author.id) as author_id, 
    group_concat(distinct tag.name order by tag.name desc separator ',') as tags

from thread

join thread_tags ON thread.id = thread_tags.thread_id
join tag ON thread_tags.tag_id = tag.id
join author_threads ON thread.id = author_threads.thread_id
join author ON author_threads.author_id = author.id

where author.id = '12'

group by thread.id

LIMIT 0 , 30

回复计数

上述查询(以及您的原始查询)仅适合检索标签列表。您可以编写一个等效的查询来检索回复计数(假设回复不是嵌套的，在这种情况下，您必须使用 MySQL 提供的任何递归查询功能，我对此不熟悉)，但要检索两者在单个查询中需要子查询:

select
    thread.title, 
    thread.id as thread_id, 
    thread.content, 
    author.username, 
    author.id, 
    (select group_concat(distinct tag.name order by tag.name separator ',')

    from thread_tags

    join tag on tag.id = thread_tags.tag_id 

    where thread_tags.thread_id = thread.id) as tags,
    (select count(1) from thread_replies where thread_id = thread.id) as reply_count

from thread

join author_threads ON thread.id = author_threads.thread_id
join author ON author_threads.author_id = author.id

where author.id = '12'

LIMIT 0 , 30

我已从此查询中删除了 group by，因为我们的聚合已移至子选择中，这意味着外部查询不再分组。