在一个查询中从三个表获取数据

我试图同时从三个表中检索数据。这些表格看起来像：

类别

id
category
messageid

messages

id
title
message

comments

id
messageid
message

我想要得到的是 1 条消息（因为我有一个WHERE基于 id 的子句3）、3 个类别（因为有 3 个类别链接到该消息）和 2 个评论（因为有 2 个评论链接到该消息）。

我尝试使用以下查询检索数据：

SELECT categories.category, messages.id, messages.title, messages.message, comments.count, comments.id as commentid, comments.message as commentmessage
FROM categories
RIGHT JOIN 
  (SELECT id, title, message
  FROM messages WHERE messaged.id = 3) messages
ON messages.id = categories.messageid
LEFT JOIN
  (SELECT count(id), id, message
  FROM comments
  GROUP BY id, message) comments
ON messages.id = comments.messageid
ORDER BY article.id DESC

然而，当运行这个查询时，我得到 6 个结果：

category      id  title      message      count  commentid  commentmessage
test          3   the title  the message  1      6          comment 1
test          3   the title  the message  1      5          comment 2
installation  3   the title  the message  1      6          comment 1
installation  3   the title  the message  1      5          comment 2
question      3   the title  the message  1      6          comment 1
question      3   the title  the message  1      5          comment 2

我期望的结果是这样的：

category      id  title      message      count  commentid  commentmessage
test          3   the title  the message  1      6          comment 1
question      3   the title  the message  1      5          comment 2
installation  3   the title  the message  1      null       null

只需三行，我就应该能够获得所有需要的数据。这可能吗？我这样做完全错了吗™？

正如评论中提到的，这里有几个问题。

首先，由于您要连接三个表，因此您得到的答案是正确的。 1 x 2 x 3 行 = 6。

其次，您对评论的汇总并没有真正汇总任何内容。正如您在结果中看到的，计数始终为 1，而我预计您认为两条评论的计数为 2。由于您是根据 id 进行分组，因此会对每个唯一 id 执行计数，该 id 始终为 1。我想你可能想对 messageid 进行分组

SELECT count(*), messageid
FROM comments
GROUP BY messageid

您需要执行另一个联接或单独的查询才能获取评论本身。

另外，正如评论中所讨论的，您通常不会通过这种方式获取信息；您通常只需进行三个查询，因为其中两个关系是一对多的。如果您的类别很短（并且您使用的是 SQL Server），您可以将类别压缩到它们自己的列中（即“测试、安装、问题”）。以下是您将如何做到这一点。

select id, title, message,
       (select CAST(category + ', ' as nvarchar(max))
        from @Categories c where messageid = m.id
        for xml path('')) as Categories
from @Messages m
where m.id = 3

实际上，有多种方法可以做到这一点，但这种方法既快速又肮脏。那么您只需要对评论进行一次额外的查询。您可以加入上一个查询并在两行中​​获取所有信息，如下所示

select m.id, title, m.message,
       (select CAST(category + ', ' as nvarchar(max))
        from @Categories c where messageid = m.id
        for xml path('')) as Categories,
        cm.message
from @Messages m
left outer join @Comments cm on m.id = cm.messageid
where m.id = 3

但同样，您可能只想进行额外的查询以避免重复信息。

最后，我想展示您可能希望如何进行评论计数。

select m.id, title, m.message,
       (select CAST(category + ', ' as nvarchar(max))
        from @Categories c where messageid = m.id
        for xml path('')) as Categories,
        CommentCount,
        cm.message
from @Messages m
left outer join 
(   
    select messageid, COUNT(*) CommentCount
    from @Comments 
    group by messageid
) rsCommentCount on rsCommentCount.messageid = m.id

最后，这里有一个链接显示该功能有效。 https://data.stackexchange.com/stackoverflow/query/75988