SQL中CASE語句強大功能詳解

iSQlServer發表於2010-03-29
SQL
概述：
SQL語句中的CASE語句與高階語言中的switch語句，是標準SQL的語法，適用與一個條件判斷有多種值的情況下分別執行不同的操作。靈活應用CASE語句可以使SQL語句變得簡潔易讀，下面在DB2環境下通過一個簡單的查詢來展示SQL CASE語句的強大功能。
 
環境：
Windows XP Professional
DB2 V9.1
 
問題：
有一個行業程式碼表，建表SQL和資料如下，要求查出程式碼別名、程式碼名、行業名、程式碼長度。程式碼別名為數字序號與大寫英文字母的序號的對映值，比如程式碼 '01'的別名就是'A','02'的別名就是'B',依次類推。
 
建表SQL和初始化資料SQL
-------------------------------------
drop table DM_HYML;
create table DM_HYML
(
  HYML_DM CHAR(2) not null,
  HYML_MC VARCHAR(100) not null,
  XYBZ    CHAR(1) not null
);
alter table DM_HYML
  add primary key (HYML_DM);
comment on table DM_HYML is
    '行業門類程式碼表';
comment on column DM_HYML.HYML_DM is
    '行業門類程式碼';
comment on column DM_HYML.HYML_MC is
    '行業門類名稱';
comment on column DM_HYML.XYBZ is
    '選用標誌';
 
delete from DM_HYML;
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('01', '農、林、牧、漁業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('03', '製造業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('02', '採礦業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('04', '電力、燃氣及水的生產和供應業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('05', '建築業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('06', '交通運輸、倉儲和郵政業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('07', '資訊傳輸、計算機服務和軟體業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('08', '批發和零售業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('09', '住宿和餐飲業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('10', '金融業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('11', '房地產業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('12', '租賃和商務服務業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('13', '科學研究、技術服務和地質勘查業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('14', '水利、環境和公共設施管理業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('15', '居民服務和其他服務業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('16', '教育', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('17', '衛生、社會保障和社會福利業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('18', '文化、體育和娛樂業', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('19', '公共管理和社會組織', 'Y');
insert into DM_HYML (HYML_DM, HYML_MC, XYBZ)
values ('20', '國際組織', 'Y');
commit;
 
實現：
 
select (case t.hyml_dm
         when '01' then 'A'
         when '02' then 'B'
         when '03' then 'C'
         when '04' then 'D'
         when '05' then 'E'
         when '06' then 'F'
         when '07' then 'G'
         when '08' then 'H'
         when '09' then 'I'
         when '10' then 'J'
         when '11' then 'K'
         when '12' then 'L'
         when '13' then 'M'
         when '14' then 'N'
         when '15' then 'O'
         when '16' then 'P'
         when '17' then 'Q'
         when '18' then 'R'
         when '19' then 'S'
         when '20' then 'T'
         when '21' then 'U'
         when '22' then 'V'
         when '23' then 'W'
         when '24' then 'X'
         when '25' then 'Y'
         when '26' then 'Z'
       end) as hydmbm,
       t.hyml_dm,
       t.hyml_mc,
       length(t.hyml_dm) as sublenth,
       '00' as zb
  from dm_hyml t
 
  將此sql程式碼儲存為C:\test.sql檔案，在DOS下進入DB2安裝目錄的bin目錄下，連結資料庫並執行（命令）此SQL，並重定向輸出查詢結果和資訊到C:\test.txt。
 
C:\IBM\SQLLIB\BIN>db2  -tvf C:\test.sql > C:\test.txt
 
執行結果：
開啟C:\test.txt檔案檢視結果：
 
select (case t.hyml_dm when '01' then 'A' when '02' then 'B' when '03' then 'C' when '04' then 'D' when '05' then 'E' when '06' then 'F' when '07' then 'G' when '08' then 'H' when '09' then 'I' when '10' then 'J' when '11' then 'K' when '12' then 'L' when '13' then 'M' when '14' then 'N' when '15' then 'O' when '16' then 'P' when '17' then 'Q' when '18' then 'R' when '19' then 'S' when '20' then 'T' when '21' then 'U' when '22' then 'V' when '23' then 'W' when '24' then 'X' when '25' then 'Y' when '26' then 'Z' end) as hydmbm, t.hyml_dm, t.hyml_mc, length(t.hyml_dm) as sublenth, '00' as zb from dm_hyml t
 
HYDMBM HYML_DM HYML_MC                                                                                                                                                                                                  SUBLENTH    ZB
------ ------- ---------------------------------- ----- --
A      01      農、林、牧、漁業                       2 00
C      03      製造業                                 2 00
B      02      採礦業                                 2 00
D      04      電力、燃氣及水的生產和供應業           2 00
E      05      建築業                                 2 00
F      06      交通運輸、倉儲和郵政業                 2 00
G      07      資訊傳輸、計算機服務和軟體業           2 00
H      08      批發和零售業                           2 00
I      09      住宿和餐飲業                           2 00
J      10      金融業                                 2 00
K      11      房地產業                               2 00
L      12      租賃和商務服務業                       2 00
M      13      科學研究、技術服務和地質勘查業         2 00
N      14      水利、環境和公共設施管理業             2 00
O      15      居民服務和其他服務業                   2 00
P      16      教育                                   2 00
Q      17      衛生、社會保障和社會福利業             2 00
R      18      文化、體育和娛樂業                     2 00
S      19      公共管理和社會組織                     2 00
T      20      國際組織                               2 00
 
  20 條記錄已選擇。
 
呵呵，CASE語句方便吧。
 
注意：DB2命令列下執行sql語句只能是一行，如果要執行多行，可以將sql儲存為檔案執行，執行的方法是：
 
1、執行SQL語句
db2  -tvf [filename].sql
2、執行儲存過程
db2 -td@ -vf [filename].sql
 
當然這些命令的選項根據需要有所不同，可以直接從命令列檢視這些選項：db2 ? OPTIONS
 選項    描述                                      預設設定
 ------  ----------------------------------------  ---------------
   -a    顯示 SQLCA                                OFF
   -c    自動落實                                  ON
   -d    檢索並顯示 XML 宣告                       OFF
   -e    顯示 SQLCODE/SQLSTATE                     OFF
   -f    讀取輸入檔案                              OFF
   -i    顯示 XML 資料並帶有縮排                   OFF
   -l    將命令記錄到歷史記錄檔案中                OFF
   -n    除去換行字元                              OFF
   -o    顯示輸出                                  ON
   -p    顯示 db2 互動式提示符                     ON
   -q    保留空格和換行符                          OFF
   -r    將輸出報告儲存到檔案                      OFF
   -s    在命令出錯時停止執行                      OFF
   -t    設定語句終止字元                          OFF
   -v    回傳當前命令                              OFF
   -w    顯示 FETCH/SELECT 警告訊息                ON
   -x    不列印列標題                              OFF
   -z    將所有輸出儲存到輸出檔案                  OFF
 
注意：
 使用 DB2OPTIONS 環境變數定製選項預設值。
 緊跟選項字母后的減號（-）使該選項關閉。
 若將減號（-）更改為加號（+），則選項
 檔案輸入方式）。
 
 
CASE和IF的區別:
在高階語言中，CASE的可以用IF來替代，但是在SQL中不行。
CASE是SQL標準定義的，IF是資料庫系統的擴充套件。
CASE可以用於SQL語句和SQL儲存過程、觸發器，IF只能用於儲存過程和觸發器。
在SQL過程和觸發器中，用IF替代CASE代價都相當的高，相當的麻煩，難以實現。
 
CASE語句應用對比：
下面做兩組查詢，每組用兩種方法來實現，一種是用case，一種是不用case，誰快誰獲勝，測試環境依然DB2 V9.1、windows server 2003。
 
第一組：查詢dj_zt表狀態為'07'或'11'、qylx_dm = '03'的所有記錄數。
A：用CASE語句
select count(case a.zt when '07' then a.bs end)+
    count(case a.zt when '11' then a.bs end)
  from dj_zt a
 where a.qylx_dm = '03'
----------------
11829
 
B：不用CASE語句
select count(*)
  from dj_zt a
 where a.qylx_dm = '03'
   and a.zt in ('07', '11')
----------------
11829
 
結果：A、B兩組耗費的代價一樣的，相比B的寫法簡潔，平局。

第二組：分別查詢dj_zt表狀態為'07'和'11'且qylx_dm = '03'的所有記錄數。
A：用CASE語句
select count(case a.zt when '07' then a.bs end),
    count(case a.zt when '11' then a.bs end)
  from dj_zt a
 where a.qylx_dm = '03
----------------
4565 7264
 
B：不用CASE語句(寫了兩條語句，掃描表兩遍,效率明顯低下)
select count(*)
  from dj_zt a
 where a.qylx_dm = '03'
   and a.zt='07'
----------------
4565 
 
select count(*)
  from dj_zt a
 where a.qylx_dm = '03'
   and a.zt='11'
----------------
7264 
 
結果：B組代價明顯高出A組很多，並且麻煩，A勝！
 
總結：通過上面兩組例項可以看出，靈活應用CASE語句可以讓SQL變得簡潔高效，而且，CASE的使用一般不會引起效能（相比沒有用CASE的語句）低下。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-630634/，如需轉載，請註明出處，否則將追究法律責任。

相關文章