在SQL Server中使用多个排序列的条件性排序。

huangapple go评论79阅读模式
英文:

Conditional Order By in sql server with multiple order columns

问题

我希望执行带有多列的条件排序,我期望一个像这样的查询:

按照
case when price is null then name desc, age desc end
case when price is not null then price desc, name desc

我们能够实现这个吗?

示例数据:

id name age price
1  sam  12  100
2  marc 34  null
3  tom  45  null
4  tom  40  null

我需要的结果是:

id name age price
1  sam  12  100   <--price不为空,所以按照price降序排序
3  tom  45  null <--price为空,所以按照name降序,age降序排序
4  tom  40  null <--price为空,所以按照name降序,age降序排序
2  marc  34  null  <--price为空,所以按照name降序,age降序排序
英文:

i wish to perform conditional order by with multiple columns
i am expecting a query like

order by 
case when price is null then name desc,age desc end
case when price is not null then price desc, name desc

can we achieve this.?

sample data

id name age price
1  sam  12  100
2  marc 34  null
3  tom  45  null
4  tom  40  null

result i need is

id name age price
1  sam  12  100   <--price is not null so sort by price desc
3  tom  45  null <--price is null so sort by name desc,age desc
4  tom  40  null <--price is null so sort by name desc,age desc
2  marc  34  null  <--price is null so sort by name desc,age desc

答案1

得分: 3

我认为这是您想要的:

按照
    如果价格为NULL,则按名称降序排列,
    如果价格不为NULL,则按价格降序排列,
    如果价格为NULL,则按年龄降序排列,
    如果价格不为NULL,则按名称降序排列。

请注意,当priceNULL时,上述变为:

按照
    按名称降序排列,
    按NULL降序排列,
    按年龄降序排列,
    按NULL降序排列。

也就是说,备用的CASE表达式会被折叠成NULL,从而使上述等同于:

按照
    按名称降序排列,
    按年龄降序排列。
英文:

I think this is what you want:

ORDER BY
    CASE WHEN price IS NULL THEN name END DESC,
    CASE WHEN price IS NOT NULL THEN price END DESC,
    CASE WHEN price IS NULL THEN age END DESC,
    CASE WHEN price IS NOT NULL THEN name END DESC;

Appreciate that when price is NULL, the above reduces to:

ORDER BY
    name DESC,
    NULL DESC,
    age DESC,
    NULL DESC;

That is, the alternate CASE expressions just collapse to NULL, leaving the above equivalent to:

ORDER BY
    name DESC,
    age DESC;

答案2

得分: 1

Tim的答案是正确的,但可以简化为:

按照(当价格为空时则为1,否则为2)排序,
价格降序,
名称降序,
年龄降序

英文:

Tim's answer is correct, but it can be simplified to:

order by (case when price is null then 1 else 2 end),
         price desc,
         name desc,
         age desc

huangapple
  • 本文由 发表于 2020年1月6日 18:09:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/59610149.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定