系列文章目錄

【SQL開發(fā)實戰(zhàn)技巧】系列（一）:關(guān)于SQL不得不說的那些事
【SQL開發(fā)實戰(zhàn)技巧】系列（二）：簡單單表查詢
【SQL開發(fā)實戰(zhàn)技巧】系列（三）：SQL排序的那些事
【SQL開發(fā)實戰(zhàn)技巧】系列（四）：從執(zhí)行計劃討論UNION ALL與空字符串&UNION與OR的使用注意事項
【SQL開發(fā)實戰(zhàn)技巧】系列（五）：從執(zhí)行計劃看IN、EXISTS 和 INNER JOIN效率，我們要分場景不要死記網(wǎng)上結(jié)論
【SQL開發(fā)實戰(zhàn)技巧】系列（六）：從執(zhí)行計劃看NOT IN、NOT EXISTS 和 LEFT JOIN效率，記住內(nèi)外關(guān)聯(lián)條件不要亂放
【SQL開發(fā)實戰(zhàn)技巧】系列（七）：從有重復(fù)數(shù)據(jù)前提下如何比較出兩個表中的差異數(shù)據(jù)及對應(yīng)條數(shù)聊起
【SQL開發(fā)實戰(zhàn)技巧】系列（八）：聊聊如何插入數(shù)據(jù)時比約束更靈活的限制數(shù)據(jù)插入以及怎么一個insert語句同時插入多張表
【SQL開發(fā)實戰(zhàn)技巧】系列（九）：一個update誤把其他列數(shù)據(jù)更新成空了？Merge改寫update！給你五種刪除重復(fù)數(shù)據(jù)的寫法！
【SQL開發(fā)實戰(zhàn)技巧】系列（十）：從拆分字符串、替換字符串以及統(tǒng)計字符串出現(xiàn)次數(shù)說起
【SQL開發(fā)實戰(zhàn)技巧】系列（十一）：拿幾個案例講講translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函數(shù)
【SQL開發(fā)實戰(zhàn)技巧】系列（十二）：三問(如何對字符串字母去重后按字母順序排列字符串？如何識別哪些字符串中包含數(shù)字？如何將分隔數(shù)據(jù)轉(zhuǎn)換為多值IN列表?)
【SQL開發(fā)實戰(zhàn)技巧】系列（十三）：討論一下常用聚集函數(shù)&通過執(zhí)行計劃看sum()over()對員工工資進行累加
【SQL開發(fā)實戰(zhàn)技巧】系列（十四）：計算消費后的余額&計算銀行流水累計和&計算各部門工資排名前三位的員工
【SQL開發(fā)實戰(zhàn)技巧】系列（十五）：查找最值所在行數(shù)據(jù)信息及快速計算總和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL開發(fā)實戰(zhàn)技巧】系列（十六）：數(shù)據(jù)倉庫中時間類型操作（初級）日、月、年、時、分、秒之差及時間間隔計算
【SQL開發(fā)實戰(zhàn)技巧】系列（十七）：數(shù)據(jù)倉庫中時間類型操作（初級）確定兩個日期之間的工作天數(shù)、計算—年中周內(nèi)各日期出現(xiàn)次數(shù)、確定當(dāng)前記錄和下一條記錄之間相差的天數(shù)
【SQL開發(fā)實戰(zhàn)技巧】系列（十八）：數(shù)據(jù)倉庫中時間類型操作（進階）INTERVAL、EXTRACT以及如何確定一年是否為閏年及周的計算
【SQL開發(fā)實戰(zhàn)技巧】系列（十九）：數(shù)據(jù)倉庫中時間類型操作（進階）如何一個SQL打印當(dāng)月或一年的日歷？如何確定某月內(nèi)第一個和最后—個周內(nèi)某天的日期？
【SQL開發(fā)實戰(zhàn)技巧】系列（二十）：數(shù)據(jù)倉庫中時間類型操作（進階）獲取季度開始結(jié)束時間以及如何統(tǒng)計非連續(xù)性時間的數(shù)據(jù)
【SQL開發(fā)實戰(zhàn)技巧】系列（二十一）：數(shù)據(jù)倉庫中時間類型操作（進階）識別重疊的日期范圍，按指定10分鐘時間間隔匯總數(shù)據(jù)
【SQL開發(fā)實戰(zhàn)技巧】系列（二十二）：數(shù)倉報表場景? 從分析函數(shù)效率一定快嗎聊一聊結(jié)果集分頁和隔行抽樣實現(xiàn)方式
【SQL開發(fā)實戰(zhàn)技巧】系列（二十三）：數(shù)倉報表場景? 如何對數(shù)據(jù)排列組合去重以及通過如何找到包含最大值和最小值的記錄這個問題再次用執(zhí)行計劃給你證明分析函數(shù)性能不一定高
【SQL開發(fā)實戰(zhàn)技巧】系列（二十四）：數(shù)倉報表場景?通過案例執(zhí)行計劃詳解”行轉(zhuǎn)列”,”列轉(zhuǎn)行”是如何實現(xiàn)的
【SQL開發(fā)實戰(zhàn)技巧】系列（二十五）：數(shù)倉報表場景?結(jié)果集中的重復(fù)數(shù)據(jù)只顯示一次以及計算部門薪資差異高效的寫法以及如何對數(shù)據(jù)進行快速分組
【SQL開發(fā)實戰(zhàn)技巧】系列（二十六）：數(shù)倉報表場景?聊聊ROLLUP、UNION ALL是如何分別做分組合計的以及如何識別哪些行是做匯總的結(jié)果行

前言

本篇文章講解的主要內(nèi)容是：如何以指定的單列或多列順序返回查詢結(jié)果、通過translate函數(shù)替換字符串、如何根據(jù)數(shù)字和字母混合字符串中的字母排序以及空值排序。
【SQL開發(fā)實戰(zhàn)技巧】這一系列博主當(dāng)作復(fù)習(xí)舊知識來進行寫作，畢竟SQL開發(fā)在數(shù)據(jù)分析場景非常重要且基礎(chǔ)，面試也會經(jīng)常問SQL開發(fā)和調(diào)優(yōu)經(jīng)驗，相信當(dāng)我寫完這一系列文章，也能再有所收獲，未來面對SQL面試也能游刃有余~。

一、以指定的次序返回查詢結(jié)果

實際提取數(shù)據(jù)或生成報表時，一般都要根據(jù)一定的順序查看，比如，想查看單位所雇員工的信息。

SELECT empno, ename, hiredate
  FROM emp
 WHERE deptno = 10
 ORDER BY hiredate ASC;
EMPNO	ENAME	HIREDATE
7782	CLARK	1981-6-9
7839	KING	1981-11-17
7934	MILLER	1982-1-23

這種語句很多人都會寫，但除了ORDER BY hiredate ASC這種寫法外，還可以寫成ORDER BY 3 ASC,意思是按第三列排序。

SELECT empno, ename, hiredate
  FROM emp
 WHERE deptno = 10
 ORDER BY 3 ASC;
EMPNO	ENAME	HIREDATE
7782	CLARK	1981-6-9
7839	KING	1981-11-17
7934	MILLER	1982-1-23

當(dāng)取值不定時，用這種方法就很方便，比如，有時取sal,有時要取comm來顯示：

SQL> 
SQL> SELECT empno, ename, sal
  2    FROM emp
  3   WHERE deptno = 10
  4   ORDER BY 3 ASC;

EMPNO ENAME            SAL
----- ---------- ---------
 7934 MILLER       1300.00
 7782 CLARK        2450.00
 7839 KING         5000.00

SQL> 
SQL> SELECT empno, ename, comm
  2    FROM emp
  3   WHERE deptno = 10
  4   ORDER BY 3 ASC;

EMPNO ENAME           COMM
----- ---------- ---------
 7782 CLARK      
 7934 MILLER     
 7839 KING       

SQL> 

對于這種需求，如果order by后使用列名，就需要注意前后保待一致，否則會給java開發(fā)人員帶來一些麻煩。比如，開發(fā)初期的語句如下：

String str=null;
str=str+"select ename,hiredate,sal"
str=str+"from emp"
str=str+"order by ename"

后來要求增加empno的顯示及排序，而我們經(jīng)常要按第一列排序，代碼需要改為：

String str=null;
str=str+"select empno,ename,hiredate,sal"
str=str+"from emp"
str=str+"order by empno"

如果語句比較復(fù)雜，會經(jīng)常忘記更改后面的order by,但使用orderby 1這種方式就沒問題。
需要注意的是，用數(shù)據(jù)來代替列位置只能用于order by子句中，其他地方都不能用。

二、按多個字段排序

如果按多列排序且有升有降怎么辦？如：按部門編號升序，并按工資降序排列。排序時有兩個關(guān)鍵字：ASC表示升序、DESC表示降序。
所以我們在order by后加兩列，并分別標(biāo)明ASC、DESC。

SQL> SELECT empno,deptno,sal,ename,job FROM emp ORDER BY 2 ASC, 3 DESC;

EMPNO DEPTNO       SAL ENAME      JOB
----- ------ --------- ---------- ---------
 7839     10   5000.00 KING       PRESIDENT
 7782     10   2450.00 CLARK      MANAGER
 7934     10   1300.00 MILLER     CLERK
 7788     20   3000.00 SCOTT      ANALYST
 7902     20   3000.00 FORD       ANALYST
 7566     20   2975.00 JONES      MANAGER
 7876     20   1100.00 ADAMS      CLERK
 7369     20    800.00 SMITH      CLERK
 7698     30   2850.00 BLAKE      MANAGER
 7499     30   1600.00 ALLEN      SALESMAN
 7844     30   1500.00 TURNER     SALESMAN
 7521     30   1250.00 WARD       SALESMAN
 7654     30   1250.00 MARTIN     SALESMAN
 7900     30    950.00 JAMES      CLERK
 1001                  test       

15 rows selected

多列排序時，若前面的列有重復(fù)值（如deptno=10有3行數(shù)據(jù)），后面的排序才有用。相當(dāng)于是通過前面的列把數(shù)據(jù)分成了幾組，然后每組的數(shù)據(jù)再按后面的列進行排序。

三、按子串排序

有一種速查法就是按顧客電話號碼尾號的順序記錄，這樣在查找的時候就可以快速縮小查詢范圍，增強顧客的認(rèn)可度。如果要按這種方法排序，應(yīng)該怎么做呢？通過函數(shù)取出后面幾位所需的信息即可。

with t as (
select 'zyd' as ename ,'18710059586' as phone from dual 
union all
select 'zyd1','18710059386' as phone from dual 
union all
select 'zyd2','18710059986' as phone from dual 
)
select ename,phone,substr(phone,-4) as 尾號
from t
order by 3

由此可見：只要能將數(shù)據(jù)查詢出來，就能根據(jù)相應(yīng)的信息排序。

四、TRANSLATE

語法格式：TRANSLATE(expr,from_string,to_string)
案例如下

select translate('zhaoyandong','yand','@#$%') from dual
TRANSLATE('ZHAOYANDONG','YAND','@#$%')
zh#o@#$%o$g

from_string與to_string以字符為單位，對應(yīng)字符一一替換。
如果to_string為空，則返回空值。

SQL> select translate('zhaoyandong','yand','') from dual;

TRANSLATE('ZHAOYANDONG','YAND','')
----------------------------------------------------------------

SQL> 

如果to_string對應(yīng)的位置沒有字符，刪除from_string中列出的字符將會被消掉。

SQL> 
SQL> select translate('zhaoyandong','yand','ya') from dual;

TRANSLATE('ZHAOYANDONG','YAND','YA')
----------------------------------------------------------------
zhaoyaog

五、按數(shù)字和字母混合字符串中的字母排序

創(chuàng)建案例數(shù)據(jù)臨時表：

with t as (
select empno||ename as vname from emp
)
select * from t
VNAME
7369SMITH
7499ALLEN
7521WARD
7566JONES
7654MARTIN
7698BLAKE
7782CLARK
7788SCOTT
7839KING
7844TURNER
7876ADAMS
7900JAMES
7902FORD
7934MILLER
1001test

這個需求就難一點了，看到里面的字母（也就是原來的列ename)嗎？要求按其中的字母（列ename)排序。
那么就要先取出其中的字母才行，我們可以用translate的替換功能，把數(shù)字與空格都替換為空：

with t as (
select empno||ename as vname from emp
)
select t.vname,translate(vname,'-0123456789','-') as tmp from t
order by 2   
VNAME                                              TMP
-------------------------------------------------- --------------------------------------------------------------------------------
7876ADAMS                                          ADAMS
7499ALLEN                                          ALLEN
7698BLAKE                                          BLAKE
7782CLARK                                          CLARK
7902FORD                                           FORD
7900JAMES                                          JAMES
7566JONES                                          JONES
7839KING                                           KING
7654MARTIN                                         MARTIN
7934MILLER                                         MILLER
7788SCOTT                                          SCOTT
7369SMITH                                          SMITH
7844TURNER                                         TURNER
7521WARD                                           WARD
1001test                                           test

15 rows selected

其實還可以通過正則regexp_replace等等方式處理，后面文章會寫出來~！

六、處理排序空值

Oracle默認(rèn)排序空值在后面，如果想把空值（如emp.comm)顯示在前面怎么辦，用NVL(comm,-1)嗎？
也許很多人都是用的這種方法，但這種方法需要對列類型及其中保存的數(shù)據(jù)有所了解才行，而且保存的數(shù)據(jù)如果有變化，該語句就要重新維護。
其實可以用關(guān)鍵字NULLS FIRST和NULLS LAST?？罩翟谇皩懛ǎ?/p>

select * from emp order by comm nulls first;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 1001 test                       2021-10-9 1                     
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30

15 rows selected

空值在后寫法：

SQL> select * from emp order by comm nulls last;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 1001 test                       2021-10-9 1                     
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20

15 rows selected

這樣寫方便的多！

七、根據(jù)條件取不同列中的值來排序

有時排序的要求會比較復(fù)雜，比如：領(lǐng)導(dǎo)對工資在1000到2000元之間的員工更感興趣，于是要求工資在這個范圍的員工要排在前面，以便優(yōu)先查看。
對于這種需求，我們可以在查詢中新生成一列，用多列排序的方法處理：

SELECT empno AS 編碼,
       ename AS 姓名,
       CASE
         WHEN sal>= 1000 AND sal < 2000 THEN
          1
         ELSE
          2
       END AS 級別,
       sal AS 工資
FROM emp
 WHERE deptno = 30
 ORDER BY 3, 4;
   編碼 姓名               級別        工資
----- ---------- ---------- ---------
 7654 MARTIN              1   1250.00
 7521 WARD                1   1250.00
 7844 TURNER              1   1500.00
 7499 ALLEN               1   1600.00
 7900 JAMES               2    950.00
 7698 BLAKE               2   2850.00

6 rows selected

可以看到，950與2850都排在了后面，也可以不顯示級別，直接把case when放在
order by中：

SELECT empno AS 編碼,
       ename AS 姓名,
       sal AS 工資
FROM emp
 WHERE deptno = 30
 ORDER BY (       CASE
         WHEN sal>= 1000 AND sal < 2000 THEN
          1
         ELSE
          2
       END),3;
   編碼 姓名              工資
----- ---------- ---------
 7654 MARTIN       1250.00
 7521 WARD         1250.00
 7844 TURNER       1500.00
 7499 ALLEN        1600.00
 7900 JAMES         950.00
 7698 BLAKE        2850.00

6 rows selected

總結(jié)

不早了，零點十分了，又是一篇SQL基礎(chǔ)文章，繼續(xù)加油！溫故而知新~文章來源地址http://www.zghlxwxcb.cn/news/detail-778950.html

到了這里，關(guān)于【SQL開發(fā)實戰(zhàn)技巧】系列（三）：SQL排序的那些事的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！