SQL語法 | PL/SQL | Function | TRIGGER | StoreProc | DBA

2009年9月16日 星期三

substring

SQL 中的 substring 函數是用來抓出一個欄位資料中的其中一部分。這個函數的名稱在不同的資料庫中不完全一樣:

  • MySQL: SUBSTR(), SUBSTRING()
  • Oracle: SUBSTR()
  • SQL Server: SUBSTRING()

最常用到的方式如下 (在這裡我們用SUBSTR()為例):

SUBSTR(str,pos): 由<str>中,選出所有從第<pos>位置開始的字元。請注意,這個語法不適用於SQL Server上。

SUBSTR(str,pos,len): 由<str>中的第<pos>位置開始,選出接下去的<len>個字元。

假設我們有以下的表格:

Geography 表格

region_name store_name
East Boston
East New York
West Los Angeles
West San Diego

例1:

SELECT SUBSTR(store_name, 3)
FROM Geography
WHERE store_name = 'Los Angeles';

結果:

's Angeles'

例2:

SELECT SUBSTR(store_name,2,4)
FROM Geography
WHERE store_name = 'San Diego';

結果:

'an D'

2009年9月4日 星期五

自我連接Self-join

 

同 一 個 表 格 連 接 到 自 己 , 這 就 稱 為 「 自 我 連 接 」 (Self-join) 。假 設 你 想 知 道 每 個 僱 員 的 上 司 名 稱 , 你 可 以 把 EMP 進 行 自 我 連 接 :

select  EMP.EMPLOYEEID  ,EMP.CHN_NAME ,EMP.TITLE   ,
EMP.MANAGER_ID 
from EMPLOYEES EMP

EMPLOYEEID CHN_NAME TITLE MANAGER_ID
1 張瑾雯 業務 2
2 陳季暄 業務經理 5
3 趙飛燕 業務 2
4 林美麗 業務 1
5 劉天王 業務經理 13
6 黎國明 業務 5
7 郭國臹 業務 5
8 蘇涵蘊 業務主管 2
9 孟庭亭 業務 5
12 賴俊良 資深工程師 2
13 何大樓 助手 1
14 王大德 工程師 2

select  EMP.EMPLOYEEID "員工編號",EMP.CHN_NAME "員工姓名",EMP.TITLE "員工職稱",'---->',
MANAGER.EMPLOYEEID "所屬主管編號",MANAGER.CHN_NAME "所屬主管姓名",MANAGER.TITLE "所屬主管職稱"
from EMPLOYEES EMP,EMPLOYEES MANAGER 
where  EMP.MANAGER_ID = MANAGER.EMPLOYEEID(+)

 

員工編號 員工姓名 員工職稱 ---->' 所屬主管編號 所屬主管姓名 所屬主管職稱
13 何大樓 助手 ----> 1 張瑾雯 業務
4 林美麗 業務 ----> 1 張瑾雯 業務
14 王大德 工程師 ----> 2 陳季暄 業務經理
12 賴俊良 資深工程師 ----> 2 陳季暄 業務經理
8 蘇涵蘊 業務主管 ----> 2 陳季暄 業務經理
3 趙飛燕 業務 ----> 2 陳季暄 業務經理
1 張瑾雯 業務 ----> 2 陳季暄 業務經理
9 孟庭亭 業務 ----> 5 劉天王 業務經理
7 郭國臹 業務 ----> 5 劉天王 業務經理
6 黎國明 業務 ----> 5 劉天王 業務經理
2 陳季暄 業務經理 ----> 5 劉天王 業務經理
5 劉天王 業務經理 ----> 13 何大樓 助手

SQL 算排名

列出每一行的排名是一個常見的需求,可惜 SQL 並沒有一個很直接的方式達到這個需求。要以 SQL 列出排名,基本的概念是要做一個表格自我連結 (self join),將結果依序列出,然後算出每一行之前 (包含那一行本身) 有多少行數。這樣講讀者聽得可能有點困惑,所以最好的方式是用一個實例來介紹。假設我們有以下的表格:

Total_Sales 表格

Name Sales
John 10
Jennifer 15
Stella 20
Sophia 40
Greg 50
Jeff 20

要找出每一行的排名,我們就打入以下的 SQL 語句:

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

結果:

Name Sales Sales_Rank
Greg 50 1
Sophia 40 2
Stella 20 3
Jeff 20 3
Jennifer 15 5
John 10 6

我們先來看 WHERE 子句。在字句的第一部分 (a1.Sales <= a2.Sales),我們算出有多少筆資料 Sales 欄位的值是比自己本身的值小或是相等。如果在 Sales 欄位中沒有同樣大小的資料,那這部分的 WHERE 子句本身就可以產生出正確的排名。

子句的第二部分,(a1.Sales=a2.Sales and a1.Name = a2.Name),則是讓我們在 Sales 欄位中有同樣大小的資料時 (像 Stella 及 Jeff 這兩筆資料),仍然能夠產生正確的排名。

EXISTS

基本上, EXISTS 是用來測試內查詢有沒有產生任何結果。如果有的話,系統就會執行外查詢中的 SQL。若是沒有的話,那整個 SQL 語句就不會產生任何結果。

EXISTS 的語法是:

SELECT "欄位1"
FROM "表格1"
WHERE EXISTS
(SELECT *
FROM "表格2"
WHERE [條件])

在內查詢中,我們並不一定要用 * 來選出所有的欄位。我們也可以選擇表格2中的任何欄位。這兩種做法最後的結果是一樣的。

來看一個例子。假設我們有以下的兩個表格:

Store_Information 表格

store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999

Geography 表格

region_name store_name
East Boston
East New York
West Los Angeles
West San Diego

而我們打入的 SQL 是:

SELECT SUM(Sales) FROM Store_Information
WHERE EXISTS
(SELECT * FROM Geography
WHERE region_name = 'West')

我們會得到以下的答案:

SUM(Sales)
2750

乍看之下,這個答案似乎不太正確,因為內查詢有包含一個 [region_name = 'West'] 的條件,可是最後的答案並沒有包含這個條件。實際上,這並沒有問題。在這個例子中,內查詢產生了超過一筆的資料,所以 EXISTS 的條件成立,所以外查詢被執行。而外查詢本身並沒有包含 [region_name = 'West'] 這個條件。