风舞残阳 2008-4-2 17:55
详细说明一下SQL中CASE语句强大功能
[b]
[color=#ff0000]概述：[/color]
[/b]
[color=#000000]leizhimin51cto技术博客[/color]
[wiki]SQL[/wiki]语句中的CASE语句与高级语言中的switch语句，是[wiki]标准[/wiki]SQL的语法，适用与一个条件判断有多种值的情况下分别执行不同的操作。灵活应用CASE语句可以使SQL语句变得简洁易读，下面在DB2[wiki]环境[/wiki]下通过一个简单的查询来展示SQLCASE语句的强大功能。
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[b]
[color=#ff0000]环境：[/color]
[/b]
[color=#000000]leizhimin51cto技术博客[/color]
Windows[wiki]XP[/wiki]Professional
[color=#000000]leizhimin51cto技术博客[/color]
DB2V9.1
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[b]
[color=#ff0000]问题：[/color]
[/b]
[color=#000000]leizhimin51cto技术博客[/color]
有一个行业代码表，建表SQL和数据如下，要求查出代码别名、代码名、行业名、代码长度。代码别名为数字序号与大写英文字母的序号的映射值，比如代码'01'的别名就是'A','02'的别名就是'B',依次[wiki]类[/wiki]推。
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
建表SQL和初始化数据SQL
[color=#000000]leizhimin51cto技术博客[/color]
-------------------------------------
[color=#000000]leizhimin51cto技术博客[/color]
droptableDM_HYML;
[color=#000000]leizhimin51cto技术博客[/color]
createtableDM_HYML
[color=#000000]leizhimin51cto技术博客[/color]
(
[color=#000000]leizhimin51cto技术博客[/color]
HYML_DMCHAR(2)notnull,
[color=#000000]leizhimin51cto技术博客[/color]
HYML_MCVARCHAR(100)notnull,
[color=#000000]leizhimin51cto技术博客[/color]
XYBZCHAR(1)notnull
[color=#000000]leizhimin51cto技术博客[/color]
);
[color=#000000]leizhimin51cto技术博客[/color]
altertableDM_HYML
[color=#000000]leizhimin51cto技术博客[/color]
addprimarykey(HYML_DM);
[color=#000000]leizhimin51cto技术博客[/color]
commentontableDM_HYMLis
[color=#000000]leizhimin51cto技术博客[/color]
'行业门类代码表';
[color=#000000]leizhimin51cto技术博客[/color]
commentoncolumnDM_HYML.HYML_DMis
[color=#000000]leizhimin51cto技术博客[/color]
'行业门类代码';
[color=#000000]leizhimin51cto技术博客[/color]
commentoncolumnDM_HYML.HYML_MCis
[color=#000000]leizhimin51cto技术博客[/color]
'行业门类名称';
[color=#000000]leizhimin51cto技术博客[/color]
commentoncolumnDM_HYML.XYBZis
[color=#000000]leizhimin51cto技术博客[/color]
'选用标志';
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
deletefromDM_HYML;
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('01','农、林、牧、渔业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('03','制造业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('02','采矿业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('04','电力、燃气及水的生产和供应业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('05','建筑业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('06','交通运输、仓储和邮政业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('07','[wiki]信息[/wiki]传输、[wiki]计算机[/wiki]服务和[wiki]软件[/wiki]业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('08','批发和零售业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('09','住宿和餐饮业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('10','金融业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('11','房地产业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('12','租赁和商务服务业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('13','[wiki]科学[/wiki]研究、技术服务和地质勘查业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('14','水利、环境和公共设施[wiki]管理[/wiki]业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('15','居民服务和其他服务业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('16','教育','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('17','卫生、社会保障和社会福利业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('18','文化、体育和娱乐业','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('19','公共管理和社会组织','Y');
[color=#000000]leizhimin51cto技术博客[/color]
insertintoDM_HYML(HYML_DM,HYML_MC,XYBZ)
[color=#000000]leizhimin51cto技术博客[/color]
values('20','[wiki]国际组织[/wiki]','Y');
[color=#000000]leizhimin51cto技术博客[/color]
commit;
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[b]
[color=#ff0000]实现：[/color]
[/b]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]select(caset.hyml_dm[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'01'then'A'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'02'then'B'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'03'then'C'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'04'then'D'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'05'then'E'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'06'then'F'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'07'then'G'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'08'then'H'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'09'then'I'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'10'then'J'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'11'then'K'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'12'then'L'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'13'then'M'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'14'then'N'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'15'then'O'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'16'then'P'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'17'then'Q'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'18'then'R'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'19'then'S'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'20'then'T'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'21'then'U'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'22'then'V'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'23'then'W'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'24'then'X'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'25'then'Y'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]when'26'then'Z'[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]end)ashydmbm,[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]t.hyml_dm,[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]t.hyml_mc,[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]length(t.hyml_dm)assublenth,[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]'00'aszb[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff]fromdm_hymlt[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
将此sql代码保存为C:\test.sql文件，在DOS下进入DB2安装目录的bin目录下，链接[wiki]数据库[/wiki]并执行（命令）此SQL，并重定向输出查询结果和信息到C:\test.txt。
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
C:\IBM\SQLLIB\BIN%26gt;db2-tvfC:\test.sql%26gt;C:\test.txt
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[b]
[color=#ff0000]执行结果：[/color]
[/b]
[color=#000000]leizhimin51cto技术博客[/color]
打开C:\test.txt文件查看结果：
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
select(caset.hyml_dmwhen'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)ashydmbm,t.hyml_dm,t.hyml_mc,length(t.hyml_dm)assublenth,'00'aszbfromdm_hymlt
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
HYDMBMHYML_DMHYML_MCSUBLENTHZB
[color=#000000]leizhimin51cto技术博客[/color]
------------------------------------------------------
[color=#000000]leizhimin51cto技术博客[/color]
A01农、林、牧、渔业200
[color=#000000]leizhimin51cto技术博客[/color]
C03制造业200
[color=#000000]leizhimin51cto技术博客[/color]
B02采矿业200
[color=#000000]leizhimin51cto技术博客[/color]
D04电力、燃气及水的生产和供应业200
[color=#000000]leizhimin51cto技术博客[/color]
E05建筑业200
[color=#000000]leizhimin51cto技术博客[/color]
F06交通运输、仓储和邮政业200
[color=#000000]leizhimin51cto技术博客[/color]
G07信息传输、计算机服务和软件业200
[color=#000000]leizhimin51cto技术博客[/color]
H08批发和零售业200
[color=#000000]leizhimin51cto技术博客[/color]
I09住宿和餐饮业200
[color=#000000]leizhimin51cto技术博客[/color]
J10金融业200
[color=#000000]leizhimin51cto技术博客[/color]
K11房地产业200
[color=#000000]leizhimin51cto技术博客[/color]
L12租赁和商务服务业200
[color=#000000]leizhimin51cto技术博客[/color]
M13科学研究、技术服务和地质勘查业200
[color=#000000]leizhimin51cto技术博客[/color]
N14水利、环境和公共设施管理业200
[color=#000000]leizhimin51cto技术博客[/color]
O15居民服务和其他服务业200
[color=#000000]leizhimin51cto技术博客[/color]
P16教育200
[color=#000000]leizhimin51cto技术博客[/color]
Q17卫生、社会保障和社会福利业200
[color=#000000]leizhimin51cto技术博客[/color]
R18文化、体育和娱乐业200
[color=#000000]leizhimin51cto技术博客[/color]
S19公共管理和社会组织200
[color=#000000]leizhimin51cto技术博客[/color]
T20国际组织200
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
20条记录已选择。
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
呵呵，CASE语句方便吧。
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
注意：DB2命令行下执行sql语句只能是一行，如果要执行多行，可以将sql保存为文件执行，执行的方法是：
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
1、执行SQL语句
[color=#000000]leizhimin51cto技术博客[/color]
db2-tvf[filename].sql
[color=#000000]leizhimin51cto技术博客[/color]
2、执行存储过程
[color=#000000]leizhimin51cto技术博客[/color]
db2-td@-vf[filename].sql
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
当然这些命令的选项根据需要有所不同，可以直接从命令行查看这些选项：db2?OPTIONS
[color=#000000]leizhimin51cto技术博客[/color]
选项描述缺省设置
[color=#000000]leizhimin51cto技术博客[/color]
-------------------------------------------------------------
[color=#000000]leizhimin51cto技术博客[/color]
-a显示SQLCAOFF
[color=#000000]leizhimin51cto技术博客[/color]
-c自动落实ON
[color=#000000]leizhimin51cto技术博客[/color]
-d检索并显示[wiki]XML[/wiki]声明OFF
[color=#000000]leizhimin51cto技术博客[/color]
-e显示SQLCODE/SQLSTATEOFF
[color=#000000]leizhimin51cto技术博客[/color]
-f读取输入文件OFF
[color=#000000]leizhimin51cto技术博客[/color]
-i显示XML数据并带有缩进OFF
[color=#000000]leizhimin51cto技术博客[/color]
-l将命令记录到历史记录文件中OFF
[color=#000000]leizhimin51cto技术博客[/color]
-n除去换行字符OFF
[color=#000000]leizhimin51cto技术博客[/color]
-o显示输出ON
[color=#000000]leizhimin51cto技术博客[/color]
-p显示db2交互式提示符ON
[color=#000000]leizhimin51cto技术博客[/color]
-q保留空格和换行符OFF
[color=#000000]leizhimin51cto技术博客[/color]
-r将输出报告保存到文件OFF
[color=#000000]leizhimin51cto技术博客[/color]
-s在命令出错时停止执行OFF
[color=#000000]leizhimin51cto技术博客[/color]
-t设置语句终止字符OFF
[color=#000000]leizhimin51cto技术博客[/color]
-v回传当前命令OFF
[color=#000000]leizhimin51cto技术博客[/color]
-w显示FETCH/SELECT警告消息ON
[color=#000000]leizhimin51cto技术博客[/color]
-x不打印列标题OFF
[color=#000000]leizhimin51cto技术博客[/color]
-z将所有输出保存到输出文件OFF
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#ff0000][b]注意：[/b][/color]
[color=#000000]leizhimin51cto技术博客[/color]
使用DB2OPTIONS环境变量定制选项缺省值。
[color=#000000]leizhimin51cto技术博客[/color]
紧跟选项字母后的减号（-）使该选项关闭。
[color=#000000]leizhimin51cto技术博客[/color]
若将减号（-）更改为加号（+），则选项
[color=#000000]leizhimin51cto技术博客[/color]
文件输入方式）。
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[b]
[color=#ff0000]CASE和IF的区别:[/color]
[/b]
[color=#000000]leizhimin51cto技术博客[/color]
在高级语言中，CASE的可以用IF来替代，但是在SQL中不行。
[color=#000000]leizhimin51cto技术博客[/color]
CASE是SQL标准定义的，IF是数据库[wiki]系统[/wiki]的扩展。
[color=#000000]leizhimin51cto技术博客[/color]
CASE可以用于SQL语句和SQL存储过程、触发器，IF只能用于存储过程和触发器。
[color=#000000]leizhimin51cto技术博客[/color]
在SQL过程和触发器中，用IF替代CASE代价都相当的高，相当的麻烦，难以实现。
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#ff0000][b]CASE语句应用对比：[/b][/color]
[color=#000000]leizhimin51cto技术博客[/color]
下面做两组查询，每组用两种方法来实现，一种是用case，一种是不用case，谁快谁获胜，[wiki]测试[/wiki]环境依然DB2V9.1、[wiki]windows[/wiki]server2003。
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff][b]第一组：查询dj_zt表状态为'07'或'11'、qylx_dm='03'的所有记录数。
[/b][/color]
[color=#008000]A：用CASE语句selectcount(casea.ztwhen'07'thena.bsend)+
count(casea.ztwhen'11'thena.bsend)
fromdj_zta
wherea.qylx_dm='03'
----------------
11829leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#008000]B：不用CASE语句selectcount(*)
fromdj_zta
wherea.qylx_dm='03'
anda.ztin('07','11')
----------------
11829leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#ff1493]结果：A、B两组耗费的代价一样的，相比B的写法简洁，平局。[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#0000ff][b]第二组：分别查询dj_zt表状态为'07'和'11'且qylx_dm='03'的所有记录数。[/b][/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#008000]A：用CASE语句selectcount(casea.ztwhen'07'thena.bsend),
count(casea.ztwhen'11'thena.bsend)
fromdj_zta
wherea.qylx_dm='03
----------------
45657264leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#008000]B：不用CASE语句(写了两条语句，扫描表两遍,效率明显低下)selectcount(*)
fromdj_zta
wherea.qylx_dm='03'
anda.zt='07'
----------------
4565leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
selectcount(*)
fromdj_zta
wherea.qylx_dm='03'
anda.zt='11'
----------------
7264
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#ff1493]结果：B组代价明显高出A组很多，并且麻烦，A胜！[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#000000]leizhimin51cto技术博客[/color]
[color=#ff0000][b]总结：通过上面两组实例可以看出，灵活应用CASE语句可以让SQL变得简洁高效，而且，CASE的使用一般不会引起性能（相比没有用CASE的语句）低下。[/b][/color]
