數據透視表之SQL高級查詢(下).xls

一一 子子查查詢詢詳詳講講 子查詢的一些特別要注意的地方 1 子查詢必須放在括號內 2 子查詢也必須放在比較操作符號的右邊 3 子查詢最多可以嵌套到255級 4 子查詢中不能使用ORDER BY子句 即ORDER BY必須位于查詢的最外層 一 單行子查詢 單行子查詢并不是最后輸出的結果只能返回一行 而是指子查詢只能返回一行 有一條規(guī)則我們必須記住 當我們用 等于 比較操作符把子查詢和父查詢嵌套在一起時 父查詢期望從子查詢那里只得到一行返回值 例例1 1 查查詢詢所所在在部部門門為為 研研究究B B部部 的的職職工工在在職職工工表表中中的的記記錄錄 select from 職工表 where 部門編號 select 部門編號 from 部門表 where 部門 研究B部 其它一些表明是單行子查詢的比較操作符有 二 多行子查詢 多行子查詢 意味著子查詢返回的結果子集可以是多行 因此 我們通常用集合比較操作符 如 IN NOT IN 把父查詢和子查詢連接起來 例例2 2 查查詢詢所所在在控控股股方方以以A A開開頭頭的的職職工工在在職職工工表表中中的的記記錄錄 select from 職工表 where 部門編號 in select 部門編號 from 部門表 where 控股方 like A 三 多重嵌套子查詢 例1和例2中 主查詢的WHERE子句中都只有一列 然而 有時需要主查詢處理多重關系 例例3 3 查查詢詢每每個個部部門門工工資資最最高高的的職職工工信信息息 select from 職工表 where 部門編號 工資 in select 部門編號 工資 from select 部門編號 max 工資 as 工資 from 職工表 group by 部門編號 注意 多重嵌套查詢 一般是自內向外 這樣寫好內層的SQL語句 再慢慢地嵌套好外層的語句 對自己的思維發(fā)散有好處 建議學員們學多重嵌套的時候 先把內部的語句復制出來測試一下 看看結果是怎么樣的 一般在oracle和MS SQL中會出現(xiàn)多列子查詢 但在excel中 只能通過嵌套和增加新的連接字段來實現(xiàn)多列子查詢 四 帶有any some 或 all 謂詞的子查詢 使用any 或all 謂詞時必須同時使用比較運算符 主要見 any大于子查詢結果中的某個值 all大于子查詢結果中的所有值 any小于子查詢結果中的某個值 any大于或等于子查詢結果中的某個值 all大于或等于子查詢結果中的所有值 any小于或等于子查詢結果中的某個值 all小于或等于子查詢結果中的所有值 any等于子查詢結果中的某個值 all等于子查詢結果中的所有值 或 any 不等于子查詢結果中的某個值 或 all 不等于子查詢結果中的任何一個值 例例4 4 查查詢詢其其他他部部門門編編號號中中比比部部門門編編號號為為A0003A0003的的某某一一職職工工的的工工資資還還要要少少的的所所有有記記錄錄 select from 職工表 where 工資 any select 工資 from 職工表 where 部門編號 A0003 and 部門編號 A0003 子查詢工資集 首先子查詢 找出部門編號為A0003 中所有職工的工資 構成一個集 合 然后進行父查詢 找出所有部門編 號不是A0003且工資少于6500或其 以下的職工記錄 注意 該查詢結果也可以用聚集函數來實現(xiàn) 首先用子查詢找出部門編號為A0003中最高工資為6500 然后在父查詢中查出所有部門編號不是A0003且工資小于6500的職工記錄 SQL語句如下 select from 職工表 where 工資 select max 工資 from 職工表 where 部門編號 A0003 and 部門編號 A0003 例例5 5 查查詢詢其其他他部部門門編編號號中中比比部部門門編編號號為為A0001A0001的的所所有有職職工工的的工工資資還還要要少少的的所所有有記記錄錄 select from 職工表 where 工資 all select 工資 from 職工表 where 部門編號 A0001 and 部門編號 A0001 注意 該查詢結果也可以用聚集函數來實現(xiàn) 首先用子查詢找出部門編號為A0001中最低工資為1300 然后在父查詢中查出所有部門編號不是A0001且工資小于1300的職工記錄 SQL語句如下 select from 職工表 where 工資 select min 工資 from 職工表 where 部門編號 A0001 and 部門編號 A0001 注意 用聚集函數查詢通常要比any 和 all查詢的效率要高 特別是大數據量的時候就比較明顯的 any 或 some all 謂詞與聚集函數 in謂詞的等價轉換關系 或 anyin max min min all not in min max max 五五 自自連連接接 比比較較難難理理解解 給給有有基基礎礎的的學學員員去去研研究究 課課上上不不講講 一個表與自身進行連接 稱為自連接 例例子子6 6 查查詢詢工工資資表表中中員員工工的的姓姓名名和和基基本本工工資資 其其中中每每行行包包含含兩兩組組員員工工的的姓姓名名和和基基本本工工資資 且且左左邊邊的的員員工工工工資資要要大大于于右右邊邊員員工工的的工工資資 select a 員工姓名 a 基本工資 b 員工姓名 b 基本工資 from 工資表 a inner join 工資表 b on a 基本工資 b 基本工資 結果表 有一個成績表 里面有學號 功課編號 學生成績三個字段 例例7 7 用用一一個個SQLSQL查查詢詢語語句句得得出出每每門門功功課課成成績績最最好好的的前前兩兩名名 select distinct a from 成績表 a where a 學號 in select top 2 成績表 學號 from 成績表 where 成績表 功課編號 a 功課編號 order by 學生成績 desc 注意 成績表 a 這里的a起到中轉站的作用 可以簡化新表的縮寫 結果表 內部的子連接 首先對學生成績進行降序排列 然后提取最好成績的前2名 select top 2 成績表 學號 from 成績表 where 成績表 功課編號 a 功課編號 order by 學生成績 desc 值得注意的是 因為是要查詢每門功課 故外部一層與內層的聯(lián)系是功課編號要相等 便出現(xiàn)了 成績表 功課編號 a 功課編號 而外面一層主要是為了提取其記錄 需要不重復的 定義了一個新的虛構表 成績表 a 而功課編號為連接條件進行連接 當我們用 等于 比較操作符把子查詢和父查詢嵌套在一起時 父查詢期望從子查詢那里只得到一行返回值 select from 職工表 where 部門編號 select 部門編號 from 部門表 where 部門 研究B部 多行子查詢 意味著子查詢返回的結果子集可以是多行 因此 我們通常用集合比較操作符 如 IN NOT IN 把父查詢和子查詢連接起來 select from 職工表 where 部門編號 in select 部門編號 from 部門表 where 控股方 like A select from 職工表 where 部門編號 工資 in select 部門編號 工資 from select 部門編號 max 工資 as 工資 from 職工表 group by 部門編號 注意 多重嵌套查詢 一般是自內向外 這樣寫好內層的SQL語句 再慢慢地嵌套好外層的語句 對自己的思維發(fā)散有好處 一般在oracle和MS SQL中會出現(xiàn)多列子查詢 但在excel中 只能通過嵌套和增加新的連接字段來實現(xiàn)多列子查詢 select from 職工表 where 工資 any select 工資 from 職工表 where 部門編號 A0003 and 部門編號 A0003 首先用子查詢找出部門編號為A0003中最高工資為6500 然后在父查詢中查出所有部門編號不是A0003且工資小于6500的職工記錄 select from 職工表 where 工資 select max 工資 from 職工表 where 部門編號 A0003 and 部門編號 A0003 select from 職工表 where 工資 all select 工資 from 職工表 where 部門編號 A0001 and 部門編號 A0001 首先用子查詢找出部門編號為A0001中最低工資為1300 然后在父查詢中查出所有部門編號不是A0001且工資小于1300的職工記錄 select from 職工表 where 工資 select min 工資 from 職工表 where 部門編號 A0001 and 部門編號 A0001 例例子子6 6 查查詢詢工工資資表表中中員員工工的的姓姓名名和和基基本本工工資資 其其中中每每行行包包含含兩兩組組員員工工的的姓姓名名和和基基本本工工資資 且且左左邊邊的的員員工工工工資資要要大大于于右右邊邊員員工工的的工工資資 select a 員工姓名 a 基本工資 b 員工姓名 b 基本工資 from 工資表 a inner join 工資表 b on a 基本工資 b 基本工資 select distinct a from 成績表 a where a 學號 in select top 2 成績表 學號 from 成績表 where 成績表 功課編號 a 功課編號 order by 學生成績 desc select top 2 成績表 學號 from 成績表 where 成績表 功課編號 a 功課編號 order by 學生成績 desc 員工部門編號職位工資 ChrisA0001研究經理20000 RitaA0002研究員3500 IreneA0003研究員3800 CalvinA0004研究員3700 SteveA0005助理研究員2000 KevinA0002高級研究員9600 AmyA0003助理研究員1500 BruceA0002高級研究員5500 MaggieA0003高級研究員6500 UraniaA0004助理研究員1200 BenA0005助理研究員2300 VickyA0003研究員3300 JohnA0004助理研究員1100 SnoopyA0005研究員3300 VincentA0002研究經理19000 PonyA0003研究員3000 GingerA0002助理研究員1000 EllaA0001助理研究員1300 BlindaA0002助理研究員1000 KenA0003高級研究員5500 AliceA0004研究員2400 EricA0005研究員3300 JackyA0002研究員3400 MaryA0003助理研究員1000 CooperA0004助理研究員1000 JonnaA0005研究員3000 BillA0002研究總監(jiān)100000 WiseA0003高級研究員6000 JoheA0002研究經理18000 CandyA0002高級研究員8000 LuolaA0004高級研究員9000