mysql - 获取超过 x 个订阅者的博客数量

Question

所以我有一个博客列表和一个订阅记录列表，用于跟踪哪些用户订阅了哪些博客。我想知道至少有两个人订阅的博客总数。以下是给定表格的列表:

CREATE TABLE IF NOT EXISTS `blogs` (
    `id` int(11) NOT NULL,
    `name` varchar(255),
    `user_id` int(11)
);

CREATE TABLE IF NOT EXISTS `subscribers` (
   `user_id` int(11) NOT NULL,
   `blog_id` int(11) NOT NULL,
);

我已经尝试了一些方法来仅使用一个查询来获取原始数字，因为我不知道如何在 PHP 中进行处理来解决这个问题。以下是我的一些尝试，但没有奏效:

#This was my attempt to just count the results of a subquery on the subscribers table (FAILED)
SELECT COUNT(*) FROM (SELECT COUNT(*) as subs_count FROM `subscribes` WHERE subs_count > 1) AS dummy_table WHERE 1;

#This was my attempt to produce a count of the number of subscribers and count that (FAILED)
SELECT COUNT(*) FROM `subscribes` WHERE count(*) >= 2 GROUP BY blog_id;

#I'm sure of how to get the number of subscribers to each blog irregardless of subscription count, that query looks as followed:
SELECT id, title, COUNT(*) as subs_count FROM `blogs`, `subscribers` WHERE `blogs`.`id` = `subscribers`.`blog_id` GROUP BY `blog_id` ORDER BY subs_count DESC;

然而，限制该查询仅返回具有 2 个或更多订阅者的博客我还无法弄清楚。感谢您的帮助和时间。

Answer 1

使用 HAVING 子句过滤 GROUP BY。

SELECT id, title, COUNT(*) as subs_count  
FROM `blogs`, `subscribers`  
WHERE `blogs`.`id` = `subscribers`.`blog_id`
GROUP BY `blog_id`
HAVING COUNT(*) >= 2
ORDER BY subs_count DESC;