mysql - 计算结果提取表的列数

Question

我需要根据获取的结果表对列进行计数。我正在尝试将其合并为 1 个查询，而不是将其分开。

下面的图片提供了更好的说明。所以假设我里面有1000条记录PostTable ，我想根据PostTable.DateTime检索最新20条记录。我对此没有任何问题。

但是根据检索到的20条记录，我想做一个COUNT函数计算 LikeID 出现了多少次ImageID 的存在就会发生找到了。

SELECT PostTable.PostID, PostTable.UserID, COUNT(LikeID), PostTable.DateTime 
FROM PostTable, LikeTable
WHERE PostTable.ImageID = LikeTable.ImageID
AND LikeTable.LikeID = ?????? // I'm stuck here

Answer 1

您需要分组，因此请尝试以下操作:

SELECT PostTable.PostID, PostTable.UserID, COUNT(LikeID) LikeCount, PostTable.DateTime 
FROM PostTable, LikeTable
WHERE PostTable.ImageID = LikeTable.ImageID
GROUP BY PostTable.PostID, PostTable.UserID, PostTable.DateTime
HAVING COUNT(LikeID) > 0

了解有关分组的更多信息:https://dev.mysql.com/doc/refman/5.1/en/group-by-handling.html

哦，考虑研究更现代的 JOIN 语法:

SELECT pt.PostID, pt.UserID, COUNT(lt.LikeID) LikeCount, pt.DateTime 
FROM PostTable pt
LEFT JOIN LikeTable lt ON pt.ImageID = lt.ImageID -- Note it's a left join now
GROUP BY pt.PostID, pt.UserID, pt.DateTime
HAVING COUNT(lt.LikeID) > 0

@sagi ^^^;)

简化示例

-- Two tables, a post can have many likes
CREATE TABLE post (postId INT);
CREATE TABLE `like` (postId INT);

-- Insert some posts and likes for some of those posts
INSERT INTO post VALUES (1),(2),(3),(4),(5),(6),(7),(8);
INSERT INTO `like` VALUES (1),(1),(3),(3),(5),(6),(7),(8);

-- Take a look at the data
SELECT * FROM post;
SELECT * FROM `like`;

-- Count the number of likes per post
SELECT p.postId, COUNT(l.postId) likes
FROM post p
LEFT JOIN `like` l ON p.postId = l.postId
GROUP BY p.postId
HAVING COUNT(l.postId) > 0; -- This removed the posts with no likes

这是您正在寻找的示例吗？