通过不按日期顺序返回结果进行分组 - Group by not returning results in date order

Group by not returning results in date order

本文关键字：结果返回顺序日期 | 更新日期: 2023-09-27

在我的项目中，当用户查看视频时，它将被插入到用于存储查看的电影详细信息的表中。

我使用下面的查询来获取当前用户按最新记录的顺序查看的电影。如果用户已经多次观看了一个视频，它应该只返回其中最新的记录。

我正在使用以下查询。

SELECT vm.watched_on,vm.movie_id,cp.channel_id,
v.title, v.videothumbnail, v.views, v.likes, v.length, v.videoid,
cp.title as videotitle, cv.thumbnail,cv.views as cvviews,cv.likes as cvlikes, cv.id,cp.starttime,cp.endtime,vm.videotype
FROM viewedmovies as vm
LEFT JOIN video as v
ON (vm.movie_id=v.videoid AND vm.user_id=1 AND vm.videotype=1 and v.permission!=-1)
LEFT JOIN channel_videos as cv
ON (vm.movie_id=cv.id AND vm.user_id=1 AND (vm.videotype=0 or vm.videotype=2))
LEFT JOIN channel_programmes as cp
ON (vm.movie_id=cp.programme_source AND (vm.videotype=0 or vm.videotype=2) AND cp.status!=-1) 
where vm.user_id=1 GROUP BY vm.user_id, vm.movie_id, vm.videotype
ORDER BY vm.watched_on desc
LIMIT 6

视频存储在两个表中:如果viewedmovies中的videotype为1，则从视频表中查询，如果videotype为0，则从 channel_programs 和channel_videos中查询。该查询返回结果，但不是按日期顺序返回。

http://sqlfiddle.com/!2/0c382/1

谁能帮我解决这个问题?

http://sqlfiddle.com/#!2/0c382/55

SELECT vm.watched_on,vm.movie_id,cp.channel_id,
v.title, v.videothumbnail, v.views, v.likes, v.length, v.videoid,
cp.title as videotitle, cv.thumbnail,cv.views as cvviews,cv.likes as cvlikes, cv.id,cp.starttime,cp.endtime,vm.videotype
FROM
(
     SELECT x.*
     FROM
     (
         SELECT user_id, movie_id, watched_on, videotype
         FROM viewedmovies
         ORDER BY watched_on DESC
     ) as x
     GROUP BY x.user_id, x.movie_id
) as vm
LEFT JOIN video as v
    ON (vm.movie_id=v.videoid AND vm.user_id=1 AND vm.videotype=1 and v.permission!=-1)
LEFT JOIN channel_videos as cv
    ON (vm.movie_id=cv.id AND vm.user_id=1 AND (vm.videotype=0 or vm.videotype=2))
LEFT JOIN channel_programmes as cp
    ON (vm.movie_id=cp.programme_source AND (vm.videotype=0 or vm.videotype=2) AND cp.status!=-1) 
WHERE vm.user_id=1 
ORDER BY vm.watched_on desc