另一个表上的访问计数不起作用

Question

我有一个博客系统的 Posts 表和 PostComments 表。我想按评论计数对帖子进行计数和排序，但我的查询不起作用。：

SELECT Posts.PostID, Posts.DateCreated, Posts.Title, Posts.Description,
Posts.Hits, (SELECT Count(CommentID) FROM PostComments WHERE 
PostComments.PostID=Posts.PostID AND PostComments.IsApproved=True) AS
CommentCount FROM Posts ORDER BY Posts.PostID DESC;

我也尝试过：

SELECT Posts.PostID, Posts.DateCreated, Posts.Title, Posts.Description,
Posts.Hits, Count([CommentID]) AS CommentCount FROM Posts INNER JOIN PostComments
ON Posts.PostID = PostComments.PostID;

但出现错误“您试图执行不包含指定表达式“PostID”的查询作为聚合函数的一部分。”

Answer 1

SELECT 
    Posts.PostID, 
    Posts.DateCreated, 
    Posts.Title, 
    Posts.Description, 
    Posts.Hits, 
    dr.CommentCount 
FROM Posts p
INNER JOIN 
    (SELECT PostID, Count(CommentID) as CommentCount FROM PostComments WHERE  
        PostComments.IsApproved=True GROUP BY PostId) dr ON dr.PostID = p.PostID
ORDER BY dr.CommentCount DESC;

Answer 2

我是一个十足的 Access 菜鸟，但请尝试按非聚合列进行分组的第二个。

    SELECT 
       Posts.PostID
       ,Posts.DateCreated
       ,Posts.Title
       ,Posts.Description
       ,Posts.Hits
       ,Count([CommentID]) AS CommentCount 
    FROM Posts 
       INNER JOIN PostComments ON Posts.PostID = PostComments.PostID
    GROUP BY
       Posts.PostID
       ,Posts.DateCreated
       ,Posts.Title
       ,Posts.Description
       ,Posts.Hits
   ORDER BY
      Count([CommentID]);

也许您必须在 MS-Access 中将 JOIN 行放入大括号中。

Answer 3

尝试

SELECT Posts.PostID, Posts.DateCreated, Posts.Title, Posts.Description,
Posts.Hits, Count([CommentID]) AS CommentCount FROM Posts INNER JOIN PostComments
ON Posts.PostID = PostComments.PostID
GROUP BY Posts.PostID, Posts.DateCreated, Posts.Title, Posts.Description, Posts.Hits
ORDER BY Count([CommentID]) DESC