Oracle查詢優化-02給查詢結果排序

1以指定的次序傳回查詢結果
- 問題
- 解決方案
- 總結
2按多個字段排序
3按子串排序
4 TRANSLATE
- 文法
- 工具
5 按數字和字母混合字元串中的字母排序
6 處理排序空值 - nulls first 和 nulls last
7 根據條件取不同列中的值來排序

2.1以指定的次序傳回查詢結果

顯示部門10中的員工姓名、職位、工資，并且按照工資升序排列，結果集如下：

SQL> select  a.ename,a.job ,a.sal from emp  a where a.deptno=10;
ENAME JOB         SAL
---------- --------- ---------
CLARK MANAGER   2450.00
KING  PRESIDENT   5000.00
MILLER CLERK   1300.00

使用order by 子句

SQL>  select  a.ename,a.job ,a.sal from emp  a where a.deptno=10 order by a.sal asc ;

ENAME JOB         SAL
---------- --------- ---------
MILLER CLERK   1300.00
CLARK MANAGER   2450.00
KING  PRESIDENT   5000.00

使用order by子句可以對結果集進行排序。預設情況下升序排列，是以asc是可選的，降序排列使用desc.
. 不一定要指定排序所基于的列名，也可以給出這列的編号，編号從1開始。

SQL>  select  a.ename,a.job ,a.sal from emp  a where a.deptno=10 order by  3 ;

ENAME JOB         SAL
---------- --------- ---------
MILLER CLERK   1300.00
CLARK MANAGER   2450.00
KING  PRESIDENT   5000.00

用數字來代替列位置隻能用于order by 子句中，其他地方都不能用。

2.2按多個字段排序

在emp表中，首先按照deptno升序排列，然後按照工資降序排列

order by子句中列出不同的排序列，使用逗号分隔

SQL> select  a.deptno,a.sal from emp  a   order by a.deptno ,a.sal desc ;

DEPTNO       SAL
------ ---------
    10   5000.00
    10   2450.00
    10   1300.00
    20   3000.00
    20   3000.00
    20   2975.00
    20   1100.00
    20    800.00
    30   2850.00
    30   1600.00
    30   1500.00
    30   1250.00
    30   1250.00
    30    950.00

14 rows selected

SQL>

多列排序時，若前面的列有重複值，後面的排序才有用，相當于是通過前面的列把資料分成了幾組，然後每組的資料再按照後面的列進行排序。
在order by中，優先順序是從左到右。如果在select清單中使用的數字位置排序，那麼這個數值不能大于select清單中項目的數目。
一般情況下都可以按照select清單中沒有的列來排序，但必須顯示的給出排序的列名。
如果在查詢中使用group by 或者distinct，則不能按照select列中

2.3按子串排序

按照字元串的某一部分對查詢結果進行排序。

比如從emp中傳回員工的名字和職位，并且按照職位字段的最後兩個字元排序。

在order by 子句中使用substr函數

SQL> select a.ename, a.job from emp a order by substr ( job, -2);
SQL> select a.ename, a.job from emp a order by substr ( job, length(job)-1);


SQL> select a.ename, a.job from emp a order by substr ( job, length(job)-1);

ENAME JOB
---------- ---------
ALLEN SALESMAN
MARTIN SALESMAN
WARD SALESMAN
TURNER SALESMAN
BLAKE MANAGER
JONES MANAGER
CLARK MANAGER
KING  PRESIDENT
SMITH CLERK
ADAMS CLERK
JAMES CLERK
MILLER CLERK
SCOTT ANALYST
FORD ANALYST

14 rows selected

SQL>

使用dbms的子串字元，可以很容易的按照字元串的一部分來排序。

2.4 TRANSLATE

TRANSLATE(string,from_str,to_str)

傳回将（所有出現的）from_str中的每個字元替換為to_str中的相應字元以後的string。
TRANSLATE 是 REPLACE 所提供的功能的一個超集。如果 from_str 比 to_str 長，那麼在 from_str 中而不在 to_str 中的額外字元将從 string 中被删除，因為它們沒有相應的替換字元。to_str 不能為空。
Oracle 将空字元串解釋為 NULL，并且如果TRANSLATE 中的任何參數為NULL，那麼結果也是 NULL。

SQL> SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual; 

TRANSLATE('ABCDEFGHIJ','ABCDEF
------------------------------
123456ghij

SQL> select translate('abcbbaadef','bad','#@') from dual;

TRANSLATE('ABCBBAADEF','BAD','
------------------------------
@#c##@@ef

b将被＃替代，a将被＠替代，d對應的值是空值，将被移走。

2.5 按數字和字母混合字元串中的字母排序

現有字母和數字混合的資料，希望按照數字或者字母部分來排序。

資料集如下：

SQL> create or replace view v as select a.ename|| ' ' ||a.deptno  as data  from emp a ;

View created

SQL> select * from v ;

DATA
---------------------------------------------------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10

14 rows selected

SQL>

按照 deptno 排序

SQL> select *
  from v
 order by replace(data,
                  replace(translate(data, '0123456789', '##########'),
                          '#',
                          ''),
                  '');

DATA
---------------------------------------------------
CLARK 10
KING 10
MILLER 10
JONES 20
FORD 20
ADAMS 20
SMITH 20
SCOTT 20
WARD 30
TURNER 30
ALLEN 30
JAMES 30
BLAKE 30
MARTIN 30

14 rows selected

SQL>

按照 ename排序

SQL> select *
  from v
 order by replace(replace(translate(data, '0123456789', '##########'),
                         '#',
                         ''),
                 '');

DATA
---------------------------------------------------
ADAMS 20
ALLEN 30
BLAKE 30
CLARK 10
FORD 20
JAMES 30
JONES 20
KING 10
MARTIN 30
MILLER 10
SCOTT 20
SMITH 20
TURNER 30
WARD 30

14 rows selected

SQL>

translate和replace函數從每一行中去掉數字或者字元，這樣就很容易的可以根據具體情況來排序。

2.6 處理排序空值 - nulls first 和 nulls last

emp表中comm字段，這個字段可以為空，需要指定是否将空值排在最後或者将空值排在最前。

oracle9i以後可以使用關鍵字 nulls first 和 nulls last 來確定null是首先排序還是最後排序，而不必考慮非空值的排序方式。

SQL>  select ename ,comm from emp order by comm desc nulls first;

ENAME      COMM
---------- ---------
SMITH 
CLARK 
FORD 
JAMES 
ADAMS 
JONES 
BLAKE 
MILLER 
SCOTT 
KING  
MARTIN   1400.00
WARD    500.00
ALLEN    300.00
TURNER      0.00

14 rows selected

SQL> select ename ,comm from emp order by comm desc nulls last;

ENAME      COMM
---------- ---------
MARTIN   1400.00
WARD    500.00
ALLEN    300.00
TURNER      0.00
SCOTT 
KING  
ADAMS 
JAMES 
FORD 
MILLER 
BLAKE 
JONES 
SMITH 
CLARK 

14 rows selected

SQL>

2.7 根據條件取不同列中的值來排序

要根據某些條件邏輯來排序，比如 job是saleman的要根據comm排序，否則按照sal排序，降序排列

SQL>   select ename, job, sal, comm
    from emp
   order by case  
              when job = 'SALESMAN' then
               comm 
              else
               sal 
            end  desc;

ENAME JOB         SAL      COMM
---------- --------- --------- ---------
KING  PRESIDENT   5000.00 
FORD ANALYST   3000.00 
SCOTT ANALYST   3000.00 
JONES MANAGER   2975.00 
BLAKE MANAGER   2850.00 
CLARK MANAGER   2450.00 
MARTIN SALESMAN   1250.00   1400.00
MILLER CLERK   1300.00 
ADAMS CLERK   1100.00 
JAMES CLERK    950.00 
SMITH CLERK    800.00 
WARD SALESMAN   1250.00    500.00
ALLEN SALESMAN   1600.00    300.00
TURNER SALESMAN   1500.00      0.00

14 rows selected

SQL>

select ename, job, sal, comm,
  case  
              when job = 'SALESMAN' then
               comm 
              else
               sal 
               end  as ordered_col 
    from emp 
   order by  ordered_col   desc ;

select ename, job, sal, comm,
  case  
              when job = 'SALESMAN' then
               comm 
              else
               sal 
               end  as ordered_col 
    from emp 
   order by  5  desc ;

Oracle查詢優化-02給查詢結果排序

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述