从多个连接的表中计算行数 - MSSQL

Question

我正在为学校项目制作一个简单的论坛，但遇到了一些查询问题。我有三个表需要连接，它们分别是“类别”、“主题”和“帖子”。

ERD

表数据：

类别

主题

帖子

即使数据库中的名称拼写有点不同，但列和表与图像中显示的相同。

当我连接第二个表（“帖子”）时，“主题”计数的结果就会出错。如果我只是按照左连接并计算“主题”的行数，那么结果就正确。

    SELECT Category.Name AS CategoryName, Category.Description AS
    CategoryDescription, COUNT(Threads.Id) AS NumberOfThreads
    FROM Category
    LEFT JOIN Threads ON Threads.FkCategoryId = Category.Id GROUP
    BY Category.Name, Category.Description

结果如下：

结果为

这将返回“Threads”表中实际行数。目前一切都很好， 但是当我添加第二个联接时：

    SELECT Category.Name AS CategoryName, Category.Description AS CategoryDescription,
    COUNT(Threads.Id) AS NumberOfThreads,
    COUNT(Posts.Id) AS NumberOfPosts
    FROM Category
    LEFT JOIN Threads ON Threads.FkCategoryId = Category.Id
    LEFT JOIN Posts ON Posts.FkThreadId = Threads.Id
    GROUP BY Category.Name, Category.Description

然后我得到了这个结果：

这显示了“帖子”中实际行数既为“主题”又为“帖子”。

我还尝试将多个查询合并成一个，如下所示：

    SELECT Category.Name AS CategoryName, Category.[Description] AS
    CategoryDescription, COUNT(Threads.Id) AS NumberOfThreads
    FROM Category
    LEFT JOIN Threads ON Category.Id = Threads.FkCategoryId
    GROUP BY Category.Name, Category.Description;

    SELECT COUNT(Posts.Id) AS NumberOfPosts
    FROM Category
    LEFT JOIN Threads ON Category.Id = Threads.FkCategoryId
    LEFT JOIN Posts ON Threads.Id = Posts.FkThreadId
    GROUP BY Category.Id;

这给了我这个结果：

这是正确的数据。我更希望将它们合并成一个查询，因为我无法从第二个查询中“评估”“NumberOfPosts”。

我得到了这个错误：

"DataBinding：'System.Data.DataRowView'不包含名称为'NumberOfPosts'的属性"

我已经阅读并尝试了很多不同的方法，但不知道如何解决。我希望有人能帮忙。

- chmodder

Answer 1

我认为你在第二个连接中错过了连接条件Post.FkUserId = Threads.FkUserId。

尝试：

 LEFT JOIN Posts ON Threads.Id = Posts.FkThreadId  AND PostFkUserId = Threads.FkUserId

基本上，您的查询匹配了更多的记录，因为您只通过 Threads.Id = Posts.FkThreadId 进行了连接。在 Post 表中，有7条记录的 FkThreadId 为 7、6、6、5、2、2、2。

- Dimt

---

啊，是的，我错过了那个。这就是我没有创建表并真正尝试它的后果。 - Nathan Fox

你的回答也非常有帮助。谢谢。 - chmodder

Answer 2

请尝试以下类似的内容：

SELECT CatThread.CategoryName, CatThread.CategoryDescription,
    CatThread.NumberOfThreads,
    COUNT(Posts.Id) AS NumberOfPosts
FROM 
   ( select Category.Id, Category.Name AS CategoryName, Category.Description AS CategoryDescription,
    COUNT(Threads.Id) AS NumberOfThreads
    from Category
      LEFT JOIN Threads ON Threads.FkCategoryId = Category.Id
    group by  Category.Id, Category.Name, Category.Description) CatThread
    LEFT JOIN Threads ON Threads.FkCategoryId = CatThread.Id
    LEFT JOIN Posts ON Posts.FkThreadId = Threads.Id
GROUP BY CatThread.CategoryName, CatThread.CategoryDescription,
    CatThread.NumberOfThreads

我没有创建表格等来确保这个工作，但我相信这个查询应该可以给你想要的结果。你可能需要调整语法等。基本思路是使用子查询在单行中获取每个类别的主题行数，然后将其与帖子表连接。

- Nathan Fox

---

我已经编辑了查询语句。Dimt是正确的，我在主查询中缺少了连接。子查询需要通过Threads表进行连接。 - Nathan Fox

谢谢。那很好用。现在我只需要研究代码来理解发生了什么，因为我以前没有真正使用过子查询 :) - chmodder