我有两个表。一个视频(lol_videos):
ID、标题、Url。。。
和一个点击表(lol_hits):
ID、视频ID、时间戳
我想根据视频表中的点击量(存储在lol_hits中)和特定时间跨度对视频进行排序。
我试过一些不同的东西,但我真的做不到。。到目前为止,我只得到一些无用的mysql错误:(
$query = "SELECT `lol_videos`.*, COUNT(lol_hits.videoID) as hit_count".
" FROM `lol_videos`".
" WHERE published > $now AND pushed > 0".
" LEFT JOIN `lol_hits`".
" ON (`lol_hits`.videoID = `lol_videos`.ID)".
" ORDER BY hit_count DESC";
$result = mysql_query($query) or die(mysql_error());
有人提出如何解决这个问题的建议吗?
这应该有效:
SELECT v.*, v2.numHits FROM (
SELECT videoID, COUNT(ID) 'numHits' FROM lol_hits GROUP BY videoID
) AS v2
JOIN lol_videos v ON (v.ID = v2.videoID)
ORDER BY v2.numHits DESC;
类似的东西?
SELECT * FROM lol_videos
LEFT JOIN lol_hits
ON lol_videos.ID = lol_hits.videoID
ORDER BY lol_hits.hit_count DESC;