Oracle的order by case when

电玩女神 2021-06-26 16:06 492阅读 0赞

order by CASE WHEN ref.IS_TOP=1 THEN 1 ELSE 0 END DESC

我用一个例子来解释这个问题，用到了`Oracle`自带的用户`scott`下的`emp`表

SQL> SELECT EMPNO, ENAME, DEPTNO
          FROM SCOTT.EMP A
         ORDER BY CASE WHEN A.DEPTNO = 20 THEN 1 ELSE 0 END DESC; 
    
             EMPNO ENAME     DEPTNO
        ---------- ---------- ----------
              7902 FORD      20
              7788 SCOTT     20
              7876 ADAMS     20
              7369 SMITH     20
              7566 JONES     20
              7839 KING      10
              7844 TURNER    30
              7900 JAMES     30
              7934 MILLER    10
              7698 BLAKE     30
              7654 MARTIN    30
    
             EMPNO ENAME     DEPTNO
        ---------- ---------- ----------
              7521 WARD      30
              7499 ALLEN     30
              7782 CLARK     10
    
        14 rows selected.

从上面这个例子能看到，`oracle`把`dept=20`的排了序，`dept`不等于`20`的，没有处理，胡乱排，因为我加了`desc` 参数，所以到倒序，在看看`asc`的效果

SQL> SELECT EMPNO, ENAME, DEPTNO
          FROM SCOTT.EMP A
         ORDER BY CASE WHEN A.DEPTNO = 20 THEN 1 ELSE 0 END ASC; 
    
             EMPNO ENAME     DEPTNO
        ---------- ---------- ----------
              7782 CLARK     10
              7900 JAMES     30
              7844 TURNER     30
              7839 KING      10
              7934 MILLER     10
              7499 ALLEN     30
              7521 WARD      30
              7654 MARTIN     30
              7698 BLAKE     30
              7788 SCOTT     20
              7566 JONES     20
    
             EMPNO ENAME     DEPTNO
        ---------- ---------- ----------
              7902 FORD      20
              7876 ADAMS     20
              7369 SMITH     20
    
        14 rows selected.

和我们预期的一致，`dept`等于20的在最下面。

## 总结： ##

假如需要让一个列的某些值排序的话，可以用这个方法哦。

--------------------

## `ORDER BY` 子句中的 `CASE WHEN THEN` ##

### 一. `THEN`后跟数字 ###

对于SQL：

create table tb(col int)
        insert tb
        select 1 union all select 2 union all select 3
         
        /**实现2、1、3这样排序**/
         
        select *
        from tb
        order by
          case col when 2 then 0
                   when 1 then 1
                   when 3 then 2
                   else        3
           end
         
        /** col ----------- 2 1 3 （所影响的行数为 3 行） **/

可以理解为分组排序

比如说，学生排队，凡是姓张的，我们给他们贴个标签“1”，凡是姓李的，我们给他们贴个标签“2”，凡是姓王的，我们给他们贴个标签“3”。然后按照 1 2 3 排序

对于表

tt(bmid int,boardid int,parentid int)

### 二. `THEN`后跟列名 ###

对于SQL

select * from tt;

结果集为

bmid        boardid     parentid    
        ----------- ----------- ----------- 
        1           1           9
        2           2           17
        5           3           18
        9           0           0
        15          0           0
        16          4           17
        17          0           0
        18          0           0
        5           3           18

而用SQL

select * from tt order by (case when parentid=0 then bmid else parentid end),parentid,bmid,boardid;

结果集为

bmid        boardid     parentid    
        ----------- ----------- ----------- 
        9           0           0
        1           1           9
        15          0           0
        17          0           0
        2           2           17
        16          4           17
        18          0           0
        5           3           18
        5           3           18

对于后面的`SQL`就等同于

select (case when parentid=0 then bmid else parentid end) as myid, * 
        from tt
        order by myid,parentid,bmid,boardid

结果集为

myid        bmid        boardid     parentid    
        ----------- ----------- ----------- ----------- 
        9           9           0           0
        9           1           1           9
        15          15          0           0
        17          17          0           0
        17          2           2           17
        17          16          4           17
        18          18          0           0
        18          5           3           18
        18          5           3           18