基于子查询计数返回MYSQL结果？

Question

有没有办法调整以下MYSQL查询，使其仅显示视频计数大于0的结果？我尝试了以下方法，但它无法识别标签。

SELECT channels.*, 
(SELECT COUNT(*) FROM videos WHERE videos.video_publisher_id = channels.channel_id) as `video_count` 
FROM channels 
WHERE  channel_active = 1
AND video_count > 0
AND channel_thumbnail IS NOT NULL
ORDER BY channel_subscribers DESC

- ORStudios

---

你可以使用 having_by 子句。 - NikuNj Rathod

@NikuNjRathod，你具体期望 havin_by 做什么？ - underscore_d

Answer 1

将子查询从 SELECT 子句移至 FROM 子句。内连接可以保证匹配。

SELECT c.*, v.video_count
FROM channels c
JOIN
(
  SELECT video_publisher_id, COUNT(*) AS video_count
  FROM videos 
  GROUP BY video_publisher_id
) v ON v.video_publisher_id = c.channel_id
WHERE c.channel_active = 1
  AND c.channel_thumbnail IS NOT NULL
ORDER BY c.channel_subscribers DESC;

- Thorsten Kettner

Answer 2

如果要按子查询结果或聚合函数值进行过滤，您可以使用HAVING。您在WHERE子句中有错误，因为MySQL不知道WHERE中的此列。

SELECT channels.*, 
(SELECT COUNT(*) FROM videos WHERE videos.video_publisher_id = channels.channel_id) as `video_count` 
FROM channels 
WHERE  channel_active = 1
AND channel_thumbnail IS NOT NULL
HAVING video_count > 0
ORDER BY channel_subscribers DESC

或者：

SELECT channels.*, COUNT(*) AS video_count
FROM channels 
INNER JOIN videos 
ON videos.video_publisher_id = channels.channel_id
WHERE  channel_active = 1
AND channel_thumbnail IS NOT NULL
GROUP BY channels.channel_id
ORDER BY channel_subscribers DESC

- Artem Ilchenko

---

执行顺序不太正确；ORDER BY应该放在最后。（否则我们将无法按列别名排序。）第一个查询使用了MySQL扩展，即在HAVING中使用了列别名，因此它是非标准的SQL。由于ORDER BY是查询中发生的最后一件事情（除了可能的LIMIT子句），所以它也应该放在查询的最后。看到ORDER BY子句后面跟着HAVING子句看起来很奇怪。如果您只想要具有视频的频道，请使用内连接，不需要使用HAVING。只有当您想要没有视频的频道时才使用外连接。 - Thorsten Kettner

Answer 3

也许这会对你有所帮助。

SELECT channels.*,video_count.count
FROM channels 
left join (SELECT channel_id,COUNT(*) count FROM videos) as `video_count` on video_count.video_publisher_id = channels.channel_id
WHERE  channel_active = 1
AND video_count.count > 0
AND channel_thumbnail IS NOT NULL
ORDER BY channel_subscribers DESC

- A.D.