今天单位一位同事问我,说在一个select里的order by 后面接了 case when是什么意思?例子如下:
- order by CASE WHEN ref.IS_TOP=1 THEN 1 ELSE 0 END DESC
- 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.
- SQL> SELECT EMPNO, ENAME, DEPTNO
- FROM SCOTT.EMP A
- ORDER BY CASE WHEN A.DEPTNO = 20 THEN 1 ELSE 0 END ASC; 2 3
-
- 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的在最下面。
总结:
假如需要让一个列的某些值排序的话,可以用这个方法哦。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20893244/viewspace-2146478/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/20893244/viewspace-2146478/