SQL中CASE语句强大功能详解

时间：2022-12-13 20:47:47

概述：

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的语句）低下。

标签：功能 cas 语句详解 sql

相关文章

