Oracle SQL排序方式与case语句 | SQL排序方式与case语句

本文介绍了Oracle SQL排序方式与case语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在理解带有case语句的oracle(12c)sql order by子句时遇到困难.我有一张包含以下数据的表格，

I am facing difficulty in understanding oracle(12c) sql order by clause with case statement.I have a table with the below data,

SELECT DEPT_NO, DEPT_NAME FROM SORTNG_LOGIC;

DEPT_NO DEPT_NAME
---------- --------------------
     1 FINANCE
     2 ACCOUNT
     3 HUMAN RESOURCE
     4 AUDIT
     5 TRAINING

我正在执行以下sql查询，以便在oracle sql开发人员上为此表添加自定义订单.

I am executing the below sql query for this table to add custom order, on oracle sql developer.

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;

这给出了以下结果:

DEPT_NO DEPT_NAME
---------- --------------------
     3 HUMAN RESOURCE
     5 TRAINING
     1 FINANCE
     4 AUDIT
     2 ACCOUNT

但是我希望结果应该是

DEPT_NO DEPT_NAME
---------- --------------------
     5 TRAINING
     3 HUMAN RESOURCE
     1 FINANCE
     4 AUDIT
     2 ACCOUNT

当我按降序对dept_name进行排序时，我认为培训"应高于人力资源".

As I am sorting the dept_name in descending order, I thought'Training' should be above 'human resource'.

我的理解哪里出错了?有人可以详细解释一下吗?

Where is my understanding going wrong? Could someone please explain this in detail?

推荐答案

如果您希望部门名称按降序排列，则必须在查询中包括该信息:

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;

没有理由将CASE的值设置为字符串.逻辑上确实需要一个数字.如果您使用字符串，则大于9的值将无法正常工作.

There is no reason for the value of the CASE to 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.

这篇关于Oracle SQL排序方式与case语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！