SQL 自定义顺序按子句 [英] SQL Custom Order By Clause
问题描述
一个简单的问题.我有一个查询带回 2 列描述"和金额"在描述中,我们有 3 个结果.
A quick Question. I have a query that brings back 2 columns 'Description' and 'Amount' In the Description we have 3 outcomes.
'黄金 - 拥有'、'青铜 - 无地'和'白银 - 确定/提供'
'Gold - owned', 'Bronze - no land' and 'Silver - identified / offered'
我希望结果按以下顺序显示金、银、铜
I would like the result to show in an order of Gold,Silver,Bronze
Order By Asc 或 Desc 不能实现这一点.有没有办法自定义 Order by 子句?
Order By Asc or Desc does not achieve this. Is there a way to customize a Order by clause?
对此有任何帮助将不胜感激,谢谢生锈了
Any Help on this Would be appreciated thanks Rusty
推荐答案
在 CASE
内部,您可以为每个分配一个数值并按升序排列.如果您需要查询大表,请考虑在 Description
上添加索引以提高排序性能.
Inside of a CASE
, you may ascribe a numeric value to each and order those ascending. If you will need to query a large table, consider adding an index on Description
to improve sorting performance.
ORDER BY
CASE
WHEN Description = 'Gold - owned' THEN 0
WHEN Description = 'Silver - identified / offered' THEN 1
WHEN Description = 'Bronze - no land' THEN 2
ELSE 99 /* Any other value (which you should not have) sorts after all */
END ASC /* And don't forget to be explicit about ASC order though it's the default */
由于这与 ORDER BY
中的普通列一样工作,如果您需要按 Amount
或其他列排序,可以附加逗号.
Since this works like a normal column in the ORDER BY
, if you needed to then sort by the Amount
or other column, it can be appended with a comma.
ORDER BY
CASE
WHEN Description = 'Gold '...
END ASC,
Amount DESC,
AnotherColumn ASC
这篇关于SQL 自定义顺序按子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!