SQL语法
- 格式：docx
- 大小：28.04 KB
- 文档页数：5
1. CASE WHEN 表达式有两种形式
--简单Case函数
CASE sex
WHEN'1'THEN'男'
WHEN'2'THEN'女'
ELSE'其他'END
--Case搜索函数
CASE
WHEN sex ='1'THEN'男'
WHEN sex ='2'THEN'女'
ELSE'其他'END
2. CASE WHEN 在语句中不同位置的用法
2.1 SELECT CASE WHEN 用法
SELECT grade, COUNT (CASE WHEN sex =1THEN1/*sex 1为男生，2位女生*/
ELSE NULL
END) 男生数,
COUNT (CASE WHEN sex =2THEN1
ELSE NULL
END) 女生数
FROM students GROUP BY grade;
2.3 WHERE CASE WHEN 用法
SELECT T2.*, T1.*
FROM T1, T2
WHERE (CASE WHEN PARE_TYPE ='A'AND
T1.SOME_TYPE LIKE'NOTHING%'
THEN1
WHEN PARE_TYPE !='A'AND
T1.SOME_TYPE NOT LIKE'NOTHING%'
THEN1
ELSE0
END) =1
2.4 GROUP BY CASE WHEN 用法
SELECT
CASE WHEN salary <=500THEN'1'
WHEN salary >500AND salary <=600THEN'2'
WHEN salary >600AND salary <=800THEN'3'
WHEN salary >800AND salary <=1000THEN'4'
ELSE NULL END salary_class, -- 别名命名
COUNT(*)
FROM Table_A
GROUP BY
CASE WHEN salary <=500THEN'1'
WHEN salary >500AND salary <=600THEN'2'
WHEN salary >600AND salary <=800THEN'3'
WHEN salary >800AND salary <=1000THEN'4'
ELSE NULL END;
3.关于IF-THEN-ELSE的其他实现
3.1 DECODE() 函数
select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')
from employees;
貌似只有Oracle提供该函数，而且不支持ANSI SQL，语法上也没CASE WHEN清晰，个人不推荐使用。
3.2 在WHERE中特殊实现
SELECT T2.*, T1.*
FROM T1, T2
WHERE (PARE_TYPE ='A'AND T1.SOME_TYPE LIKE
'NOTHING%')
OR
(PARE_TYPE !='A'AND T1.SOME_TYPE NOT LIKE
'NOTHING%')
有TA, TB两表，假设均有三个栏位id, name, remark. 现在需要把TB表的name, remark 两个栏位通过id关联，更新到TA表的对应栏位。
建表脚本：
[sql]view plaincopyprint?
1.drop table TA;
2.create table TA
3.(
4.id number not null,
varchar(10) not null,
6.remark varchar(10) not null
7.);
8.
9.drop table TB;
10.create table TB
11.(
12.id number not null,
varchar(10) not null,
14.remark varchar(10) not null
15.);
16.
17.truncate table TA;
18.insert into TA values(1, 'Aname1', 'Aremak1');
19.insert into TA values(2, 'Aname2', 'Aremak2');
mit;
21.
22.truncate table TB;
23.insert into TB values(1, 'Bname1', 'Bremak1');
24.insert into TB values(3, 'Bname3', 'Bremak3');
mit;
26.
27.select * from TA;
28.select * from TB;
SQLServer/Oracle版本的Update写法分别如下：
1. SQLServer
[sql]view plaincopyprint?
1.update TA set name=, remark=b.remark from TA a inner join TB b on a
.id = b.id
或者
[sql]view plaincopyprint?
1.update TA set name=, remark=b.remark from TA a, TB b where a.id = b
.id
注意不要在被更新表的的栏位前面加别名前缀，否则语法静态检查没问题，实际执行会报错。
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "" could not be bound.
2. Oracle
[sql]view plaincopyprint?
1.update TA a set(name, remark)=(select , b.remark from TB b where b.
id=a.id)
2.where exists(select 1 from TB b where b.id=a.id)
注意如果不添加后面的exists语句，TA关联不到的行name, remark栏位将被更新为NULL 值，如果name, remark栏位不允许为null，则报错。
这不是我们希望看到的。
[sql]view plaincopyprint?
1.--when name, remark is not null, cause error.
2.--if allow null, rows in TA not matched will be update to null.
3.update TA a set(name, remark)=(select , b.remark from TB b where b.
id=a.id);
可考虑的替代方法：
[sql]view plaincopyprint?
1.update TA a set name= nvl((select from TB b where b.id=a.id), a.na
me);
2.update TA a set remark= nvl((select b.remark from TB b where b.id=a.id),
a.remark);
如果TA.id, TB.id是unique index或primary key
可以使用视图更新的语法：
[sql]view plaincopyprint?
1.ALTER TABLE TA ADD CONSTRAINT TA_PK
2. PRIMARY KEY (
3. ID
4.)
5. ENABLE
6. VALIDATE
7.;
8.
9.ALTER TABLE TB ADD CONSTRAINT TB_PK
10. PRIMARY KEY (
11. ID
12.)
13. ENABLE
14. VALIDATE
15.;
16.
17.update (select , as newname,
18.a.remark, b.remark as newremark from TA a, TB b where a.id=b.id)
19.set name=newname, remark=newremark;。
