php - MySQL 左连接计数与条件

Question

我有两个表:

1. cc_videos 字段:id、challenge_id、upload_date、owner_id

2. 带有字段 id、video_id、vote_date 的 cc_video_votes

“cc_videos”中的字段“id”对应于“cc_video_votes”中的字段“video_id”，我有这样的声明:

SELECT cc_videos.*, COUNT(video_id) AS votes_count
FROM cc_videos LEFT JOIN cc_video_votes
ON cc_videos.id = cc_video_votes.video_id
GROUP BY cc_videos.id
ORDER BY votes_count desc

现在一切正常 - 列出所有按投票率最高的视频排序的视频。现在我想设置条件以仅列出来自“cc_videos”的视频 WHERE challenge_id 匹配变量“$challenge_id”，但是当我设置这样的条件时它返回 0 个结果:

SELECT cc_videos.*, COUNT(video_id) AS votes_count
FROM cc_videos WHERE challenge_id = "$challenge_id"
LEFT JOIN cc_video_votes
ON cc_videos.id = cc_video_votes.video_id
GROUP BY cc_videos.id
ORDER BY votes_count desc

我是否正确使用了 WHERE 子句？

Answer 1

不，你不是。

SELECT cc_videos.*, COUNT(video_id) AS votes_count
FROM cc_videos 
LEFT JOIN cc_video_votes
ON cc_videos.id = cc_video_votes.video_id
WHERE cc_videos.challenge_id = "$challenge_id"
GROUP BY cc_videos.id
ORDER BY votes_count desc

WHERE 语法应保留在子句中，check documentation here