复杂的(ish)加入问题: [英] Complex(ish) Joins Question:

问题描述

我在mySQL数据库中具有下表(nb:这些是汇总的，因此它们与该问题相关，一些列已被省略).

I have the following table in a mySQL database, (nb: these are summarised so they are relevant to this question, some columns have been omitted).

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)

现在要由某个作者获取主题，我通常会这样做:

Now to get threads by a certain Author, I normally do this:

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'

这很好，但是当我尝试获取与这些线程关联的标签时:

This works fine, however when I try getting the tags associated with these threads:

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

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

It only shows the first thread, and all the tags associated with this author in one column.

我在做什么错了?

此外，如果我想计算作者对每个线程的答复数，怎么办?

Also If I wanted to count the number of replies the author got for each thread how would this be done?

推荐答案

由于在查询(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.

这篇关于复杂的(ish)加入问题:的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！