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

Is there a way to adjust the following MYSQL query so that it only shows results where the video count is greater than 0? I have tried the following but it doesn't recognise

有没有办法调整下面的MYSQL查询，使它只显示视频计数大于0的结果?我试过以下方法，但它不识别

video_count

video_count

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

3 个解决方案

#1

Move your subquery from the SELECT clause to the FROM clause. An inner join guarantees matches.

将子查询从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;

#2

Maybe this will help you.

也许这对你有帮助。

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

#3

For filter by subquery result or aggregation function value you can use HAVING. You have error in WHERE clause because MySQL don't know about this column in WHERE

通过子查询结果或聚合函数值进行筛选，您可以使用。你在WHERE子句中有错误，因为MySQL不知道这个列在哪里。

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

Or:

或者:

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

#1