我有3个表。
Video (Id, ViewCount, Location)
Likes (Id, Video, User)
Location (Id, Name)
我如何查询排名最高的四个视频(视频总数)?ViewCount + count(like . user)按视频分组。Id,并返回指定位置的前4个结果。
我试过了:
SELECT Video.Id, sum(Video.ViewCount + count(Likes.User)) as Points From Video
Left Join Likes
ON Likes.Video=Video.Id
WHERE Video.Location=30
GROUP BY Video.Id
ORDER BY Points DESC
LIMIT 4;
但是我得到一个无效的使用组函数。有人有什么建议吗?
SELECT id,
viewcount +
(
SELECT COUNT(*)
FROM likes l
WHERE l.video = v.id
) AS points
FROM video v
WHERE location = 30
ORDER BY
points DESC, id
LIMIT 4