由于在查询(GROUP_CONCAT)中使用了汇总，因此正在对查询进行分组.由于没有group by子句，因此您的组就是整个结果集(因此可以看到作者使用的每个标记).由于MySQL允许在分组语句中使用非分组列，因此不会出现错误，但不会获得所需的查询.

Because you're using an aggregate in your query (GROUP_CONCAT), your query is being grouped. Since you have no group by clause, your group is the entire result set (hence seeing every tag the author has used). Because MySQL allows for using non-grouped columns in grouped statements, you aren't getting an error, but you aren't getting the query that you want.

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

In order to retrieve the proper results, you need to group your query on 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的名称.这看起来不漂亮，但是可以兼容.

This should work in MySQL, though it's not ANSI-compliant SQL since you're using non-grouped columns in the select clause without any aggregation. You can leave this as-is, or you can write more compliant SQL and use something like max around all of the columns other than thread.id. This won't look as pretty, but it will be compliant.

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提供的任何递归查询功能(我不熟悉)，但是要同时检索两者在单个查询中需要子查询:

Reply Count

The above query (along with your original queries) are suitable only for retrieving the tag list. You could write an equivalent query to retrieve the reply count (assuming that replies aren't nested, in which case you'll have to use whatever recursive querying capabilities that MySQL provides, which I'm not familiar with), but to retrieve both in a single query requires subqueries:

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，因为我们的汇总已移至子选择中，这意味着外部查询不再分组.

I've removed the group by from this query because our aggregate has been moved into a subselect, meaning that the outer query is no longer grouped.