Oracle sql order by with case 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34708058/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle sql order by with case statement
提问by Johnyzhub
I am facing difficulty in understanding oracle(12c) sql order by clause with case statement. I have a table with the below data,
我在理解带有 case 语句的 oracle(12c) sql order by 子句时遇到了困难。我有一张包含以下数据的表格，
SELECT DEPT_NO, DEPT_NAME FROM SORTNG_LOGIC;
DEPT_NO DEPT_NAME
---------- --------------------
1 FINANCE
2 ACCOUNT
3 HUMAN RESOURCE
4 AUDIT
5 TRAINING
I am executing the below sql query for this table to add custom order, on oracle sql developer.
我正在为此表执行以下 sql 查询以在 oracle sql 开发人员上添加自定义订单。
SELECT DEPT_NO, DEPT_NAME FROM SORTNG_LOGIC ORDER BY (
CASE DEPT_NAME
WHEN 'ACCOUNT' THEN '1'
WHEN 'AUDIT' THEN '2'
WHEN 'FINANCE' THEN '3'
ELSE '4' END
)DESC;
This is giving the below result :
这给出了以下结果：
DEPT_NO DEPT_NAME
---------- --------------------
3 HUMAN RESOURCE
5 TRAINING
1 FINANCE
4 AUDIT
2 ACCOUNT
But I expected that, the result should be
但我预计，结果应该是
DEPT_NO DEPT_NAME
---------- --------------------
5 TRAINING
3 HUMAN RESOURCE
1 FINANCE
4 AUDIT
2 ACCOUNT
As I am sorting the dept_name in descending order, I thought'Training' should be above 'human resource'.
当我按降序对 dept_name 进行排序时，我认为“培训”应该高于“人力资源”。
Where is my understanding going wrong? Could someone please explain this in detail?
我的理解哪里出了问题？有人可以详细解释一下吗？
采纳答案by Gordon Linoff
If you want the department name in descending order, then you have to include that information in the query:
如果您希望部门名称按降序排列，则必须在查询中包含该信息：
ORDER BY (CASE DEPT_NAME
WHEN 'ACCOUNT' THEN 1
WHEN 'AUDIT' THEN 2
WHEN 'FINANCE' THEN 3
ELSE 4
END) DESC,
DEPT_NAME DESC;
There is no reason for the value of the CASEto be a character string. The logic really calls for a number. If you use strings, then values larger than 9 will not work as you expect them to.
的值没有理由CASE是字符串。这个逻辑确实需要一个数字。如果您使用字符串，则大于 9 的值将不会像您期望的那样工作。
回答by Techie
Try this with decode function, basically does the same thing.
用解码功能试试这个，基本上做同样的事情。
SELECT DEPT_NO, DEPT_NAME
FROM SORTNG_LOGIC
ORDER BY
decode (DEPT_NAME,'ACCOUNT','1','AUDIT','2','FINANCE','3','4') DESC;
