系列文章目錄

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

前言

本篇文章介紹一些SQL開發(fā)基礎(chǔ)的內(nèi)容，講解的主要內(nèi)容是：NULL空值的運(yùn)算、coalesce比nvl的優(yōu)勢(shì)、拼接列實(shí)現(xiàn)批量腳本、如何通過rownum限制返回的行數(shù)以及如何正確的使用rownum從表中隨機(jī)返回 n 條記錄。
【SQL開發(fā)實(shí)戰(zhàn)技巧】這一系列博主當(dāng)作復(fù)習(xí)舊知識(shí)來進(jìn)行寫作，畢竟SQL開發(fā)在數(shù)據(jù)分析場(chǎng)景非常重要且基礎(chǔ)，面試也會(huì)經(jīng)常問SQL開發(fā)和調(diào)優(yōu)經(jīng)驗(yàn)，相信當(dāng)我寫完這一系列文章，也能再有所收獲，未來面對(duì)SQL面試也能游刃有余~。

一、查詢表中所有的行與列

進(jìn)行查詢操作之前， 我們先看一下表結(jié)構(gòu)，我這里查詢的是oracle官方案例用戶scott下的表。

SQL> desc scott.emp;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO					   NOT NULL NUMBER(4) 編碼
 ENAME						    VARCHAR2(10)	名稱
 JOB						    VARCHAR2(9)		工作
 MGR						    NUMBER(4)		主管
 HIREDATE					    DATE			聘用日期
 SAL						    NUMBER(7,2)		工資
 COMM						    NUMBER(7,2)	提成
 DEPTNO 					    NUMBER(2)	部門編碼

如果領(lǐng)導(dǎo)要看員工所有信息。這個(gè)操作很簡(jiǎn)單，大家應(yīng)該都會(huì)。只要用select*就可以返回目標(biāo)表中所有的列，查詢語(yǔ)句及執(zhí)行結(jié)果如下：

SQL> select * from scott.emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 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
 1001 test                       2021-10-9 1                     

15 rows selected

如果想要修改日期的顯示格式，也可以通過to_char*等函數(shù)來實(shí)現(xiàn)，這些后面會(huì)有單獨(dú)一章講解，其次，如果你發(fā)現(xiàn)你通過sqlplus無法展示的如我這樣一行顯示完全，可以通過設(shè)置set linesize、set pagesize、col ename for a20等等設(shè)置頁(yè)面格式的命令自定義，或則通過plsql的命令行訪問，博主就是用的plsql命令行展示的！

二、從表中檢索部分行

如果想看公司有多少銷售人員，那么在查詢數(shù)據(jù)時(shí)只需加一個(gè)過濾條件就可以。職位列是job,銷售人員條件就是WHERE job='SALESMAN':

SQL>  SELECT * FROM emp WHERE job = 'SALESMAN';

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 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
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30

三、查找空值

如果要查詢某一列為空的數(shù)據(jù)怎么辦呢？比如，返回提成(comm)為空的數(shù)據(jù)。

SQL> SELECT* FROM emp WHERE comm= NULL ;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------

從步驟1中明顯能看到comm有為null的數(shù)據(jù)，為什么這里查不到呢？
原因是NULL是不能用"="運(yùn)算符的，要用IS NULL判斷，正確的寫法如下。

SQL> select * from emp where comm is null;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 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
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 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
 1001 test                       2021-10-9 1                     

11 rows selected

NULL不支持加、減、乘、除、大小比較、相等比較，否則只能為空。

SQL> select * from dept where 1>=null;

DEPTNO DNAME          LOC
------ -------------- -------------

SQL> select * from dept where 1<=null;

DEPTNO DNAME          LOC
------ -------------- -------------

SQL> select * from dept where 1+null>=0;

DEPTNO DNAME          LOC
------ -------------- -------------

SQL> select * from dept where 1+null<=0;

DEPTNO DNAME          LOC
------ -------------- -------------

SQL> select * from dept where 1*null>=0;

DEPTNO DNAME          LOC
------ -------------- -------------

SQL> select * from dept where 1*null<=0;

DEPTNO DNAME          LOC
------ -------------- -------------

SQL> 

當(dāng)然了，在使用其他函數(shù)的時(shí)候最好測(cè)試一下有NULL時(shí)會(huì)返回什么結(jié)果。

SQL> select replace('abcde','b',null) from dual;

REPLACE('ABCDE','B',NULL)
--------------------------------
acde

SQL> select greatest(1,null) from dual;

GREATEST(1,NULL)
----------------

SQL> 

可以看到，不同的函數(shù)對(duì)NULL的支持也不一樣，所以大家遇到NULL時(shí)最好測(cè)試一下，而不是僅憑經(jīng)驗(yàn)或猜測(cè)！

四、拿”返回個(gè)值中第一個(gè)不為空的值”證明coalesce比nvl好用

對(duì)于下面sql：

SELECT coalesce(comm,0) FROM emp;

可能有人會(huì)問為什么用coalesce而不用nvl？當(dāng)然是coalesce更好用了！拿”返回個(gè)值中第一個(gè)不為空的值”這個(gè)需求來說：

SQL> with t as (
  2  select null as a,null as b,'z' as c,null as d,'f' as e,null as f from dual
  3  union all
  4  select null as a,null as b,null as c,'y' as d,null as e,'a' as f from dual
  5  union all
  6  select null as a,null as b,null as c,null as d,'d' as e,'s' as f from dual
  7  )
  8  select coalesce(a,b,c,d,e,f) from t
  9  ;

COALESCE(A,B,C,D,E,F)
---------------------
z
y
d

可以看到，相對(duì)nvl來說，coalesce支持多個(gè)參數(shù)，能很方便地返回第一個(gè)不為空的值。如果上面的語(yǔ)句改用nvl,就要嵌套很多層。

SELECT nvl(nvl(nvl(nvl(nvl(a,b),c),d),e),f) FROM t;

五、查找滿足多個(gè)條件的行

簡(jiǎn)單的查詢，寫起來容易，那復(fù)雜一點(diǎn)的呢？
比如，我們要查詢部門10中的所有員工、所有得到提成的員工以及部門20中工資不超過2000美元的員工。
這是三個(gè)條件的組合，符合上述任一條件即可。
我們把這三個(gè)條件整理成邏輯表達(dá)式的形式：（部門10中的員工OR所有得到提成的員工OR(工資<=2000and部門號(hào)=20))
那我們這么寫這個(gè)需求：

SELECT *
  FROM EMP
 WHERE (DEPTNO = 10 OR COMM IS NOT NULL OR (SAL <= 2000 and DEPTNO = 20));

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 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
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

9 rows selected

對(duì)于多個(gè)條件的組合，要使用括號(hào)，這樣在更改維護(hù)語(yǔ)句時(shí)可以不必再考慮優(yōu)先級(jí)問題，而且可以很容易地借助各種工具找到各組合條件的起止位置。

六、從表中檢索部分列

前面我們都是取表中所有的列，但實(shí)際的場(chǎng)景中，常常只需要返回部分列的數(shù)據(jù)就可以。如只需員工編碼、員工名稱、雇傭日期、工資。所以一般要明確指定查詢哪些列，而不是用"*"號(hào)來代替。另外，明確要返回的列也會(huì)使語(yǔ)句的維護(hù)更簡(jiǎn)單，而不必每次看到語(yǔ)句時(shí)都需要查看表結(jié)構(gòu)才知道會(huì)返回什么數(shù)據(jù)。

SQL> SELECT empno, ename, hiredate, sal FROM emp WHERE deptno = 10;

EMPNO ENAME      HIREDATE          SAL
----- ---------- ----------- ---------
 7782 CLARK      1981-6-9      2450.00
 7839 KING       1981-11-17    5000.00
 7934 MILLER     1982-1-23     1300.00

七、為列取別名

不是每個(gè)人都能看懂那些簡(jiǎn)寫的字母字段什么意思，所以有時(shí)候需要給列取個(gè)別名。可以如下面所示在as后面跟別名，也可以不要as,直接在列名后跟別名即可。

SQL> SELECT ename AS 姓名, deptno AS 部門編號(hào),sal AS 工資,comm AS 提成 FROM emp;

姓名       部門編號(hào)        工資        提成
---------- ---- --------- ---------
SMITH        20    800.00 
ALLEN        30   1600.00    300.00
WARD         30   1250.00    500.00
JONES        20   2975.00 
MARTIN       30   1250.00   1400.00
BLAKE        30   2850.00 
CLARK        10   2450.00 
SCOTT        20   3000.00 
KING         10   5000.00 
TURNER       30   1500.00      0.00
ADAMS        20   1100.00 
JAMES        30    950.00 
FORD         20   3000.00 
MILLER       10   1300.00 
test                      

15 rows selected

八、在 WHERE 子句中引用取別名的列

寫報(bào)表時(shí)，經(jīng)常會(huì)加上各種條件，而直接在條件中使用別名比列名（如：d001,n002)要清晰得多，引用別名時(shí)千萬別忘了嵌套一層，因?yàn)檫@個(gè)別名是在SELECT之后才有效的．

SQL> 
SQL> SELECT *
  2    FROM (SELECT sal AS 工資, comm  AS 提成 from emp) X
  3   WHERE 工資 <1000;

       工資        提成
--------- ---------
   800.00 
   950.00 

如果你不嵌套一層，會(huì)報(bào)錯(cuò)的！如下：

SQL> SELECT sal AS 工資, comm  AS 提成 from emp
  2   WHERE 工資 <1000;
SELECT sal AS 工資, comm  AS 提成 from emp
 WHERE 工資 <1000

ORA-00904: "工資": 標(biāo)識(shí)符無效

九、拼接列實(shí)現(xiàn)批量腳本

若有人不喜歡看表格式的數(shù)據(jù)，希望返回的數(shù)據(jù)都像"CLARK的工作是MANAGER"這樣的顯示。我們可以用字符串連接符"||"來把各列拼在一起。

SQL> SELECT ename || '的工作是'|| job AS msg FROM emp WHERE deptno = 10 ;

MSG
---------------------------------------------------
CLARK的工作是MANAGER
KING的工作是PRESIDENT
MILLER的工作是CLERK

當(dāng)然，拼接列還有更多意義，比如我平時(shí)對(duì)某個(gè)用戶下所有表收集統(tǒng)計(jì)信息，我就可以這么寫腳本來生成批量腳本：

select 'begin'||chr(13)|| 
  'dbms_stats.lock_table_stats(ownname =>'''||a.OWNER||''',tabname =>'''||a.TABLE_NAME||''');'||chr(13)||'END;'||chr(13)||'/'||chr(13)||'prompt '||rownum
from dba_tables a where a.OWNER in('ZYD');
SQL> select 'begin'||chr(13)||
  2    'dbms_stats.lock_table_stats(ownname =>'''||a.OWNER||''',tabname =>'''||a.TABLE_NAME||''');'||chr(13)||'END;'||chr(13)||'/'||chr(13)||'prompt '||rownum
  3  from dba_tables a where a.OWNER in('ZYD');

'BEGIN'||CHR(13)||'DBMS_STATS.LOCK_TABLE_STATS(OWNNAME=>'''||A.OWNER||''',TABNAM
--------------------------------------------------------------------------------
begin
dbms_stats.lock_table_stats(ownname =>'ZYD',tabname =>'TEST_ORACLE_HIVE');
END;
/
prompt 1
begin
dbms_stats.lock_table_stats(ownname =>'ZYD',tabname =>'TEST_HIVE_ORACLE');
END;
/
prompt 2
begin

十、在 SELECT 語(yǔ)句中使用case when條件邏輯

有時(shí)為了更清楚地區(qū)分返回的信息，需要做如下處理。
如：當(dāng)職員工資小于或等于2000美元時(shí)，就返回消息“過低“，大于或等于4000美元時(shí)，就返回消息“過高”，如果在這兩者之間，就返回"OK"。
類似這種需求也許會(huì)經(jīng)常遇見，處理這樣的需求可以用CASE WHEN來判斷轉(zhuǎn)化

select ename,
       sal,
       CASE
         WHEN sal <= 2000 THEN
          '過 低'
         WHEN sal > = 4000 THEN
          '過高 '
         ELSE
          ' OK '
       END AS status
  FROM emp
 WHERE deptno = 10;
ENAME            SAL STATUS
---------- --------- ------
CLARK        2450.00  OK
KING         5000.00 過高
MILLER       1300.00 過 低

這種方式還常用在報(bào)表中，比如要按工資分檔次統(tǒng)計(jì)人數(shù)：

SELECT 檔次, COUNT(*) AS 人數(shù)
  from (SELECT (CASE
                 WHEN sal <= 1000 THEN
                  '0000-1000'
                 WHEN sal <= 2000 THEN
                  '1000-2000'
                 WHEN sal <= 3000 THEN
                  '2000-3000'
                 WHEN sal <= 4000 THEN
                  '3000-4000'
                 WHEN sal <= 5000 THEN
                  '4000-5000'
                 ELSE
                  '好高'
               END) AS 檔次,
               ename,
               sal
          FROM emp)
 GROUP BY 檔次
 ORDER BY 1;

檔次              人數(shù)
--------- ----------
0000-1000          2
1000-2000          6
2000-3000          5
4000-5000          1
好高               1

十一、限制返回的行數(shù)

在查詢時(shí)，并不要求每次都要返回所有的數(shù)據(jù)，比如，進(jìn)行抽查的時(shí)候會(huì)要求只返回兩條數(shù)據(jù)。
我們可以用偽列rownum來過濾，rownum依次對(duì)返回的每一條數(shù)據(jù)做一個(gè)標(biāo)識(shí)。

SQL> 
SQL> SELECT * FROM emp WHERE rownum<=2;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30

如果直接用rownum=2來查詢會(huì)出現(xiàn)會(huì)什么情況？

SQL> SELECT * FROM emp WHERE rownum=2;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------

SQL> 

因?yàn)閞ownum是依次對(duì)數(shù)據(jù)做標(biāo)識(shí)的，就像上學(xué)時(shí)依據(jù)考分排名一樣，需要有第一名，后面才會(huì)有第二名。所以，要先把所有的數(shù)據(jù)取出來，才能確認(rèn)第二名。
正確地取第二行數(shù)據(jù)的查詢應(yīng)該像下面這樣，先生成序號(hào)：

SQL> SELECT *
  2    FROM (SELECT rownum AS sn, emp.* FROM emp WHERE rownum <= 2)
  3   WHERE sn = 2;

        SN EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
         2  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30

SQL> 

十二、你真的會(huì)從表中隨機(jī)返回 n 條記錄嗎

我們這里的目標(biāo)是隨機(jī)返回N條記錄，大多開發(fā)者在這里都會(huì)誤認(rèn)為自己平時(shí)隨機(jī)取數(shù)據(jù)的方式對(duì)了！接下來我們一步步分析。
我們可以先用dbms_random來對(duì)數(shù)據(jù)進(jìn)行隨機(jī)排序，然后取其中三行。

SELECT   empno,ename
FROM (select empno,ename FROM emp  ORDER BY  dbms_random.value()) WHERE rownum <= 3 ;

有人會(huì)問：為什么要嵌套一層呢？直接這樣用多好。

SELECT empno , ename FROM emp WHERE rownum <= 3 ORDER BY dbms_random.value();

你可以運(yùn)行一下看， 為了方便觀察， 我們對(duì)得到的結(jié)果進(jìn)行排序 ， 運(yùn)行下面的語(yǔ)句就可以。

SELECT *
  FROM (SELECT empno, ename
          FROM emp
         WHERE rownum <= 3
         ORDER BY dbms_random.value())
 ORDER BY 1;

多運(yùn)行幾次，會(huì)發(fā)現(xiàn)是不是每次得到的數(shù)據(jù)都一樣，而不是隨機(jī)？為了便于解釋，我們先對(duì)上面的語(yǔ)句進(jìn)行等價(jià)改寫：

SELECT empno, ename, dbms_random.value() ran
  FROM emp
 WHERE rownum <= 3
 ORDER BY ran;

查詢語(yǔ)句中這幾處的執(zhí)行順序?yàn)椋?/p>

• SELECT
• ROWNUM
• ORDER BY

也就是說，要先取出數(shù)據(jù)，然后生成序號(hào)，最后才是排序。我們可以通過子查詢把排序前后的序號(hào)分別取出來對(duì)比。

SELECT rownum AS 排序后, 排序前, empno AS 編碼, ename 姓名, ran AS 隨機(jī)數(shù)
  FROM (SELECT rownum AS 排序前, empno, ename, dbms_random.value() ran
          FROM emp
         WHERE rownum <= 3
         ORDER BY ran);

同樣，你可以運(yùn)行幾次，看是不是與剛才描述的一致。因此，正確的寫法是：先隨機(jī)排序，再取數(shù)據(jù)。

SELECT empno, ename
  FROM (SELECT empno, ename FROM emp ORDER BY dbms_random.value())
 WHERE rownum <= 3;

錯(cuò)誤的寫法是： 先取數(shù)據(jù)， 再隨機(jī)排序 。

SELECT empno , ename FROM emp WHERE rownum <= 3 ORDER BY dbms_random.value();

十三、模糊查詢，使用escape轉(zhuǎn)譯字符

有如下臨時(shí)表：

with t as (
SELECT 'ABCEDF' AS vname FROM dual
UNION ALL
SELECT '_BCEFG' AS vname FROM dual
UNION ALL
SELECT '_BCBPF' AS vname FROM dual
UNION ALL
SELECT '_\BCEDF' AS vname FROM dual
UNION ALL
SELECT 'XYCEG' AS vname FROM dual )
select * from t 

要求一： 查出 vname 中包含字符串 " CED " 的。

with t as (
SELECT 'ABCEDF' AS vname FROM dual
UNION ALL
SELECT '_BCEFG' AS vname FROM dual
UNION ALL
SELECT '_BCBPF' AS vname FROM dual
UNION ALL
SELECT '_\BCEDF' AS vname FROM dual
UNION ALL
SELECT 'XYCEG' AS vname FROM dual )
select * from t where  vname like '%CED%';

VNAME
-------
ABCEDF
_\BCEDF

要求二：查出vname中包含字符串"BCE"的。這里我們需要注意，””在模糊查詢是會(huì)被當(dāng)做通配符的，“%”代表替代一個(gè)或多個(gè)字符“”替代一個(gè)字符。所以這個(gè)需求我們需要用轉(zhuǎn)譯字符來轉(zhuǎn)譯“”字符,我們可以使用escape關(guān)鍵字把’\’標(biāo)識(shí)為轉(zhuǎn)譯字符，那么查詢?nèi)缦拢?/p>

with t as (
SELECT 'ABCEDF' AS vname FROM dual
UNION ALL
SELECT '_BCEFG' AS vname FROM dual
UNION ALL
SELECT '_BCBPF' AS vname FROM dual
UNION ALL
SELECT '_\BCEDF' AS vname FROM dual
UNION ALL
SELECT 'XYCEG' AS vname FROM dual )
select * from t where  vname like '\_BCE%' escape '\';
VNAME
-------
_BCEFG

要求三：查出vname中包含字符串"_\BCE"的。根據(jù)上面解釋，我們可以這么寫！

with t as (
SELECT 'ABCEDF' AS vname FROM dual
UNION ALL
SELECT '_BCEFG' AS vname FROM dual
UNION ALL
SELECT '_BCBPF' AS vname FROM dual
UNION ALL
SELECT '_\BCEDF' AS vname FROM dual
UNION ALL
SELECT 'XYCEG' AS vname FROM dual )
select * from t where  vname like '\_\\BCE%' escape '\';
VNAME
-------
_\BCEDF

對(duì)于類似“%”的轉(zhuǎn)譯我們也可以參照這么寫。

總結(jié)

以上就是本章內(nèi)容，文章寫起來雖然麻煩，但是寫完了回頭一看還是很有成就感~文章來源地址http://www.zghlxwxcb.cn/news/detail-793301.html

到了這里，關(guān)于【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（二）：簡(jiǎn)單單表查詢的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！