Oracle SQL 中的自定义顺序

Custom Order in Oracle SQL

提问by Rudy

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.

我需要根据货币订购交易。但是，我需要实现一个自定义 order by，这使 USD 始终排在最前面，其余部分应按 asc 排序。

for example :

例如 ：

• BHT
• USD
• MYR
• JYP

• BHT
• 美元
• 马币
• JYP

should be sorted like :

应该排序为：

• USD
• BHT
• JPY
• MYR

• 美元
• BHT
• 日元
• 马币

Is there a simple way to handle this?

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

回答by a_horse_with_no_name

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

or a bit more compact but Oracle specific:

或者更紧凑但特定于 Oracle：

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.

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

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

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

其中使用“字母顺序”排序。这是有效的，因为字符在数字之后排序。（使用'AAA'而不是也'001'可以工作）。

回答by Grzegorz W

To make sure Your sort is "flexible" and will work with all currencies do this:

为了确保您的排序是“灵活的”并且适用于所有货币，请执行以下操作：

SELECT <columns>
FROM <tableName>
ORDER BY DECODE(currencyColumn,'USD', 1, 2), currencyColumn

回答by David Oneill

A more detailed way of doing this, if you are interesting in sorting certain values to the beginning or end, but have those sorted in their group:

一种更详细的方法，如果您对将某些值排序到开头或结尾感兴趣，但将它们按组排序：

order by
  case when currency in ('USD', 'CAD') 
    then '000'||currency
  when currency in ('ZWD', 'HTG')
    then 'ZZZ'||currency
  else currency 
  end

This will put the USD and CAD at the top of the list (sorted), ZWD and HTG at the bottom, and the rest sorted between those.

这将把美元和加元放在列表的顶部（排序），ZWD 和 HTG 放在底部，其余的在它们之间排序。

回答by mishok

One more variant with regexp like function FIELD()in MySQL:

另一个FIELD()在 MySQL 中使用 regexp like 函数的变体：

select
meas_code,
to_number(regexp_replace(meas_code, replace('(meas1,meas2,meas3)', ',', '|'), instr(replace('(meas1,meas2,meas3)', ',', '|'), meas_code))) ordr
from (select cast(column_value as varchar2(10)) as meas_code from xmltable('''meas1'',''meas2'',''meas3'''))
order by 2

回答by Robert

Maybe this will help you:

也许这会帮助你：

order by decode(currency, 'USD', 1, 2)

or using case

或使用 case

  order by 
      case 
        when currency = 'USD' then 1 
        else 2
      end

回答by DaveG

You could do the following:

您可以执行以下操作：

SELECT 
  * 
FROM
  yourtable
ORDER BY
  REPLACE(FIND_IN_SET(currency,'USD,BHT,JPY,MYR'),0,'Z')

回答by SurfingSanta

I needed to do the same, but with multiple columns, and found Grzegorz W's answer the best for this, with the following simple addition:

我需要做同样的事情，但有多个列，并发现 Grzegorz W 的答案是最好的，有以下简单的补充：

SELECT <columns>
FROM <tableName>
ORDER BY DECODE(currencyColumn,'USD', 1, 2), currencyColumn, anotherColumn;