mysql - 连接两个 mysql 查询，按第二个查询的结果对第一个查询进行排序

Question

我最初有一个 Web 服务，它运行第一个查询(帖子详细信息)，在其结果的 while 循环内，我运行第二个查询来检索帖子上的评论数量。我需要尝试将两者结合起来，因为现在我必须按评论数量来订购网络服务。

1. SELECT ReportID, Title, Description, posts.Pic, DatePosted, posts.UserID, FName, SName, users.Pic as userPic, 
                photoWidth, photoHeight
                FROM posts
                INNER JOIN Users 
                ON Users.UserID = posts.UserID 
                WHERE private = 0
                ORDER BY ReportID Desc
                LIMIT ?, 10

2. SELECT COUNT(ReportID) as numComments FROM Comments WHERE ReportID =? AND Comment IS NOT NULL

我不确定如何实现这一目标。我需要创建一个派生表吗？

我的初步尝试:

SELECT ReportID, Title, Description, posts.Pic, DatePosted, posts.UserID, FName, SName, users.Pic as userPic, 
                photoWidth, photoHeight, numComments 
                FROM posts
                INNER JOIN Users 
                ON Users.UserID = posts.UserID 
                WHERE private = 0 AND numComments = (SELECT COUNT(ReportID) as numComments FROM Comments WHERE ReportID = ReportID AND Comment IS NOT NULL)
                ORDER BY numComments DESC

这给出了字段列表中未知列 numComments 的问题

帖子:

- ReportID (primary)
- Title
- Description
- Pic
- private
- DatePosted (epoch)
- photoWidth
- photoHeight

评论:

- CommentID (primary)
- UserID
- ReportID (linking key)
- Comment (can be null if type = 'like')
- dateposted (epoch)
- type ('comment' or 'like')

Answer 1

如果我正确理解你的问题，我认为你想要的是以下内容:

SELECT Posts.*, count(Comments.ReportID) as CommentCount FROM Posts 
LEFT JOIN Comments
ON Comments.ReportID = Posts.ReportID
WHERE private = 0
GROUP BY Comments.ReportID
ORDER BY CommentCount, ReportID Desc;

显然，您需要调整它以包含您想要的所有字段以及您想要执行的任何其他联接。

这是一个demo .

这将获取所有帖子以及每个帖子中的评论数。