oracle ORDER BY DECODE(BLAH, [COLUMN NUMBER]) 在单列查询上。它是如何工作的？

ORDER BY DECODE(BLAH, [COLUMN NUMBER]) on a single column query. How does it work?

提问by Azeworai

Hi I need help to understand the decode part of a query that goes something like the following.

嗨，我需要帮助来理解类似于以下内容的查询的解码部分。

SELECT ax.animal_code    
FROM  raw_animal_xref ax,
      animal_xref_type axt
WHERE ax.animal_mnemonic = l_animal_mnemonic -- Example 'COUGAR'
AND   ax.animal_code_type = axt.animal_code_type
ORDER BY DECODE (animal_type, 
                l_type_to_be_matched, -1, -- Example 'CATS'
                l_current_type, 0, -- Example 'BIG CATS'
                nvl(axt.type_search_priority, 100)) ASC; -- EXAMPLE 'Big Cats' Priority is 1

Since this query returns only 1 query, I'm a little stumped on how the ORDER BY works with the different non-existing column numbers supplied by DECODE. The query works as a cursor to find a unique code for the animal in question given a animal mnemonic the current animal type and the type to be matched with.

由于此查询仅返回 1 个查询，因此我对 ORDER BY 如何与 DECODE 提供的不同的不存在的列号一起工作感到有些困惑。给定动物助记符、当前动物类型和要匹配的类型，该查询用作光标以查找相关动物的唯一代码。

I'm thinking that DECODE returns the different column numbers to ORDER BY with and I tried experimenting with a different simple single column selects on some other tables with ORDER by '-1', '0' and '100' and the ORDER by seems to fail for 0 and 100. Why does it work with -1 or any of the other numbers?

我认为 DECODE 将不同的列号返回给 ORDER BY，我尝试在其他一些表上尝试不同的简单单列选择，ORDER by '-1'、'0' 和 '100' 和 ORDER by 似乎0 和 100 失败。为什么它适用于 -1 或任何其他数字？

Hope someone can explain this to me. Thanks!

希望有人可以向我解释这一点。谢谢！

采纳答案by Doug Porter

It creates the set that will be used for ordering.

它创建将用于排序的集合。

If animal_type = l_type_to_be_matched, use a -1 as the sort value for that row
else if animal_type = l_current_type, use 0 as the sort value of that row
else if axt.type_search_priority is null then use 100 as the sort value of that row
else use axt.type_search_priority as the sort value for that row.

如果animal_type = l_type_to_be_matched，则使用-1 作为该行的排序值，
否则，如果animal_type = l_current_type，则使用0 作为该行的排序值，
否则，如果axt.type_search_priority 为空，则使用100 作为该行的排序值，
否则使用axt.type_search_priority 作为该行的排序值。

It gives a kind of conditional sort cirteria. Often used to ensure that certain items are always at the top or bottom of a sorted set.

它给出了一种条件排序标准。通常用于确保某些项目始终位于已排序集合的顶部或底部。

回答by Gary Myers

The ORDER BY can use one of three expressions. Firstly an alias of the select list, secondly the number of a column in the select list or thirdly an SQL expression which may use zero or more columns from the source tables.

ORDER BY 可以使用三个表达式之一。首先是选择列表的别名，其次是选择列表中的列数，或者第三是可能使用源表中零个或多个列的 SQL 表达式。

So when you use ORDER BY SUBSTR(col,2,10) you order by a 10 character substring of the column value starting from the second character.

因此，当您使用 ORDER BY SUBSTR(col,2,10) 时，您将从第二个字符开始按列值的 10 个字符子字符串进行排序。

Similarly when use

使用时同样

ORDER BY decode(col,'DOG',1,'CAT',2,'EEL', 3, 5)

you translate DOG into value 1, CAT into value 2, EEL into value 3 and others into value 5. Then order by the resulting numeric value (ie DOG first, then CAT, then EEL, finally anything else).

您将 DOG 转换为值 1，将 CAT 转换为值 2，将 EEL 转换为值 3，将其他转换为值 5。然后按结果数值排序（即首先是 DOG，然后是 CAT，然后是 EEL，最后是其他任何值）。

You can achieve the same ordering using

您可以使用

ORDER BY decode(col,'DOG','A','CAT','B','EEL', 'C', 'D')

回答by Dave Costa

Referring to this portion of your question:

参考你问题的这一部分：

I'm thinking that DECODE returns the different column numbers to ORDER BY with and I tried experimenting with a different simple single column selects on some other tables with ORDER by '-1', '0' and '100' and the ORDER by seems to fail for 0 and 100. Why does it work with -1 or any of the other numbers?

我认为 DECODE 将不同的列号返回给 ORDER BY，我尝试在其他一些表上尝试不同的简单单列选择，ORDER by '-1'、'0' 和 '100' 和 ORDER by 似乎0 和 100 失败。为什么它适用于 -1 或任何其他数字？

Your confusion is understandable; but no, the values returned by the DECODE are not interpreted as column numbers.

你的困惑是可以理解的；但不，由 DECODE 返回的值不会被解释为列号。

Oracle supports a little syntactic shortcut, in which columns of the result set can be referred to positionally in an ORDER BY clause. So for example this:

Oracle 支持一个小的语法快捷方式，其中可以在 ORDER BY 子句中按位置引用结果集的列。所以例如这个：

SELECT a, b FROM some_table ORDER BY 1,2

is the same as:

是相同的：

SELECT a, b FROM some_table ORDER BY a,b

However, this positional notation can only be done with non-negative integer literals. If the ORDER BY includes an expression which produces a numeric value, it will not be interpreted as a column number, but as an actual value to be sorted on. Also, negative numeric literals are interpreted as sort values, not as column numbers.

但是，这种位置符号只能用非负整数文字来完成。如果 ORDER BY 包含一个产生数值的表达式，它不会被解释为列号，而是作为要排序的实际值。此外，负数字文字被解释为排序值，而不是列号。

SELECT * FROM table ORDER BY -1will sort all rows on the constant value -1 (effectively no sorting).

SELECT * FROM table ORDER BY -1将对常量值 -1 的所有行进行排序（实际上没有排序）。

SELECT * FROM table ORDER BY 0will return an error because 0 is an invalid column number.

SELECT * FROM table ORDER BY 0将返回错误，因为 0 是无效的列号。

SELECT * FROM table ORDER BY 1will sort all rows on the value of the first column in the table.

SELECT * FROM table ORDER BY 1将根据表中第一列的值对所有行进行排序。

SELECT * FROM table ORDER BY 100will sort all rows on the value of the 100th column in the table, or return an error if there are fewer than 100 columns.

SELECT * FROM table ORDER BY 100将根据表中第 100 列的值对所有行进行排序，如果少于 100 列则返回错误。

SELECT * FROM table ORDER BY TO_NUMBER('1')will sort all rows on the constant value 1.

SELECT * FROM table ORDER BY TO_NUMBER('1')将对常量值 1 上的所有行进行排序。

I haven't tested this out thoroughly, but looking at some execution plans, it appears that you can even specify a non-integer numeric literal, and it will be rounded down and used as a column number.

我还没有彻底测试过这个，但是查看一些执行计划，似乎您甚至可以指定一个非整数数字文字，它将被四舍五入并用作列号。

SELECT * FROM table ORDER BY 1.5appears to sort on the value of the first column.

SELECT * FROM table ORDER BY 1.5似乎按第一列的值排序。