MySQL查询本月找到最喜欢的视频

I have a table of songs each with a unique id and publish date

我有一个歌曲表,每个歌曲都有一个唯一的ID和发布日期

id | name | pub_date

Also a table containing likes which have the video id, user id making the like and also the user who uploaded the videos id:

此外,还有一个表格,其中包含具有视频ID,用户ID等的内容,以及上传视频ID的用户:

song_id | user_id | songuser_id

The query I need to make is to find the 10 most liked videos that have been uploaded in the current month. Here is what I have so far:

我需要查询的是查找当前月份上传的10个最喜欢的视频。这是我到目前为止:

SELECT songs.id, songs.name, songs.pub_date, COUNT(likes.song_id) as likecount 
FROM `songs` INNER JOIN `likes` on songs.id = likes.song_id
WHERE MONTH(CAST(songs.pub_date as date)) = MONTH(NOW()) 
AND YEAR(CAST(songs.pub_date as date)) = YEAR(NOW()) 
ORDER BY likecount DESC LIMIT 5

But I only seem to return 1 result. I think the issue I'm having is to do with grouping the likes but I can't figure it out. Any help would be appreciated.

但我似乎只返回1个结果。我认为我遇到的问题是对喜欢分组,但我无法弄明白。任何帮助,将不胜感激。

Thanks, James.

2 个解决方案

#1

You need to have GROUP BY clause since you aggregated on of the columns in your SELECT clause,

您需要拥有GROUP BY子句,因为您聚合了SELECT子句中的列,

SELECT..
FROM...JOIN...
WHERE...
GROUP BY songs.id, songs.name, songs.pub_date
ORDER BY...
LIMIT...

#2

Have you tried using DISTINCT/GROUP?

你尝试过使用DISTINCT / GROUP吗?

SELECT 
   songs.id, songs.name, songs.pub_date, COUNT(likes.song_id) as likecount
FROM
   songs
INNER JOIN likes ON likes.song_id = songs.id
WHERE
   MONTH(CAST(songs.pub_date as date)) = MONTH(NOW()) 
   AND 
   YEAR(CAST(songs.pub_date as date)) = YEAR(NOW()) 
GROUP BY
   songs.id  
ORDER BY
   likecount DESC 
LIMIT 5

#1