mysql - 为什么 MySQL COUNT 在计算连接列时返回的结果是我预期的两倍？

Question

我正在尝试在 wordpress 中构建一个用户通知系统。我有一个连接 wp_posts 表中的行的查询，这些行具有 publish 的 post_status、1 的 post_author 和 user_notification 的 post_type。有两行满足这些要求。 total_unread 列始终为 4，整个查询仅返回 1 行。我究竟做错了什么？这是一个SqlFiddle

这是表结构

CREATE TABLE `wp_posts` (
  `ID` bigint(20),
  `post_author` bigint(20) DEFAULT '0',
  `post_content` longtext NULL,
  `post_status` varchar(20) DEFAULT 'publish',
  `post_type` varchar(20) DEFAULT 'post'
) ENGINE=InnoDB;
INSERT INTO wp_posts
(ID, post_author, post_content, post_status, post_type)
VALUES(1, 1, 'John Smith would like to be friends!', 'publish', 'user_notification');
INSERT INTO wp_posts
(ID, post_author, post_content, post_status, post_type)
VALUES(2, 1, 'Sally Miller shared your post!', 'publish', 'user_notification');

这里是查询:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID, wp_posts.post_status, COUNT(unread.post_status) as total_unread 
FROM wp_posts  
 JOIN wp_posts AS unread ON unread.post_author = 1 AND unread.post_status = 'publish' AND unread.post_type = 'user_notification'
WHERE wp_posts.post_author IN (1)  
      AND wp_posts.post_type = 'user_notification' 
ORDER BY wp_posts.ID DESC LIMIT 0, 5

Answer 1

问题是您的 JOIN 正在创建每个帖子与其他帖子的叉积，因此您得到 4 (2x2) 的计数。如果您在表中有 3 个相似的行，您将获得 9 的计数。尝试以 SELECT * 而不是当前字段运行查询，您将看到 4 行。

我真的不明白你为什么要使用JOIN？你想达到什么目的？如果您只想计算“未读”帖子(由 post_status='publish' 表示)，此查询就足够了:

SELECT post_author, SUM(IF(post_status='publish',1,0)) as total_unread 
FROM wp_posts 
WHERE post_type = 'user_notification' 
GROUP BY post_author

或者更简单:

SELECT post_author, COUNT(*) as total_unread 
FROM wp_posts 
WHERE post_type = 'user_notification' AND post_status='publish'
GROUP BY post_author