Oracle SQL 中的自定义顺序

问题描述

我需要根据货币订购交易.但是我需要实现一个自定义 order by，这使得 USD 总是排在最前面，其余的应该按 asc 排序.

I need to order transaction based on the currency.However I need to implement a custom order by, which makes the USD to always comes on the top, and the rest should be ordered asc.

例如:

• BHT
• 美元
• 马币
• JYP

应该排序为:

• 美元
• BHT
• 日元
• 马币

有没有简单的方法来处理这个问题?

Is there a simple way to handle this?

推荐答案

不知道这样算不算简单:

Don't know if this qualifies as simple:

order by
    case
       when currency = 'USD' then 1
       when currency = 'BHT' then 2
       when currency = 'JPY' then 3
       when currency = 'MYR' then 4
       else 5
    end

或者更紧凑但特定于 Oracle 的:

or a bit more compact but Oracle specific:

order by decode(currency, 'USD', 1, 'BHT', 2, 'JPY', 3, 'MYR', 4, 5)

上述使用数字定义排序顺序的解决方案不会自动对 case/decode 表达式中未提及的货币进行正确排序.

The above solution using numbers to defined the sort order will not automatically sort currencies correctly that aren't mentioned in the case/decode expression.

为了简单地将美元放在前面而不关心其余部分，生成的"订单标准也必须是一个字符值.在这种情况下，您可以使用以下内容:

To simply put USD at the front and don't care about the rest, the "generated" order criteria must be a character value as well. You can use the following in that case:

order by
    case
       when currency = 'USD' then '001'
       else currency
    end

使用字母顺序"排序.这是有效的，因为字符在数字之后排序.(使用 'AAA' 而不是 '001' 也可以).

Which uses an "alphabetical" ordering. This works because characters are sorted after the number digits. (Using 'AAA' instead of '001' would work as well).

这篇关于Oracle SQL 中的自定义顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！