我想按以下两种质量的表订购。
提问于 2020-04-06 16:26:42
上面是我通过查询得到的结果
·输出应按两种不同的质量进行排序o首先按非空排名的数量降序o其次按排名的总和按升序排序
上图是结果应该是什么样子的。
使用的服务器: Mysql
这是我的问题，到目前为止我所尝试的任何帮助都是非常感谢的
select
snippettitle as category
, if(cntrank>limitnumber,null,cntrank) as counts
,if(viewrank>limitnumber,null,viewrank) as views
, if(likesrank>limitnumber,null,likesrank) likes
, if(dislikesrank>limitnumber,null,dislikesrank) dislikes
,if(commentrank>limitnumber,null,commentrank) comment_count
from ( select
snippettitle
,RANK() OVER (ORDER BY cnt DESC) AS cntrank
,RANK() OVER (ORDER BY views DESC) AS viewrank
,RANK() OVER (ORDER BY likes DESC) AS likesrank
,RANK() OVER (ORDER BY dislikes DESC) AS dislikesrank
,RANK() OVER (ORDER BY comment_count DESC) AS commentrank
from homework7a) tmp
where
(cntrank <= limitnumber
|| viewrank <= limitnumber
|| likesrank <= limitnumber
|| dislikesrank <= limitnumber
|| commentrank <= limitnumber
)
order by counts desc,views desc,likes desc ,dislikes desc,comment_count desc;
回答 1
Stack Overflow用户
发布于 2020-04-06 17:08:30
你的第一个条件：
按非空排名数量降序排列的
可以简化为：
按空排名数量升序排列的
所以我相信你想要这个：
select *
from yourquery
order by
(counts is null) + (views is null) + (likes is null) + (dislikes is null) + (comment_count is null),
coalesce(counts, 0) + coalesce(views, 0) + coalesce(likes, 0) + coalesce(dislikes, 0) + coalesce(comment_count, 0)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/61064432
复制相关文章
腾讯云开发者
