以下是一些常用的苹果CMS SQL查询语句:(适应于V8/V10)
1、查询视频分类及其下的视频数量
SELECT c.catid, c.catname, COUNT(v.id) AS video_count FROM cms_video v LEFT JOIN cms_video_cat c ON v.catid = c.catid GROUP BY c.catid, c.catname;
2、查询用户的观看历史记录
SELECT v.id, v.title, v.thumb, v.create_time, h.update_time FROM cms_history h LEFT JOIN cms_video v ON h.video_id = v.id WHERE h.user_id = 1 ORDER BY h.update_time DESC LIMIT 10;
3、查询视频排行榜
SELECT v.id, v.title, v.thumb, v.play_count, v.create_time FROM cms_video v ORDER BY v.play_count DESC LIMIT 10;
4、查询视频评论及其回复
SELECT c.*, r.* FROM cms_comment c LEFT JOIN cms_reply r ON c.id = r.comment_id WHERE c.video_id = 1;
5、查询用户收藏的视频
SELECT v.id, v.title, v.thumb, v.create_time FROM cms_favorite f LEFT JOIN cms_video v ON f.video_id = v.id WHERE f.user_id = 1;
以上是一些常用的苹果CMS SQL查询语句,你可以根据自己的需求进行修改和使用。