Oracle SQL中的自定义订单 [英] Custom Order in Oracle SQL

问题描述

我需要根据货币订购交易. 但是，我需要实施一个自定义订单，以使美元始终排在首位，其余的应按升序订购.

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
• 美元
• MYR
• JYP

应按以下顺序排序:

• 美元
• BHT
• JPY
• MYR

有一种简单的方法可以解决这个问题吗?

Is there a simple way to handle this?

推荐答案

不知道这是否简单:

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)

上述使用数字定义排序顺序的解决方案将不会自动正确地区分大小写/解码表达式中未提及的货币.

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.

要简单地将USD放在首位而不关心其余部分，生成的"订单条件也必须是字符值.在这种情况下，您可以使用以下内容:

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中的自定义订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！