-
video表结构
-
user表结构
-
需求分解
- 查询上传视频最多的用户top10;
- 将top10的数据表与video表join,得到上传视频的相关信息;
- 将上述表信息对view进行排序并取前20;
-
需求实现
- 查询上传视频最多的用户top10;
SELECT
videos,uploader
FROM
gulivideo_user_orc
ORDER BY videos DESC
LIMIT 10 ;
- [ ] 将top10的数据表与video表join,得到上传视频的相关信息;
SELECT
t1.uploader,
videoid,
views
FROM
gulivideo_orc t2
JOIN ()t1
ON t2.uploader = t1.uploader ;
- [ ] 将上述表信息对view进行排序并取前20;
SELECT
uploader,
videoid
FROM
t3
ORDER BY views DESC
LIMIT 20 ;
- 最终代码
SELECT
uploader,
videoid,
views
FROM
(SELECT
t1.uploader,
videoid,
views
FROM
gulivideo_orc t2
INNER JOIN
(SELECT
videos,uploader
FROM
gulivideo_user_orc
ORDER BY videos DESC
LIMIT 10) t1
ON t2.uploader = t1.uploader) t3
ORDER BY views DESC
LIMIT 20 ;
- 执行效果
- 结束