1.
NVL( string1, replace_with)
功能:如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值,如果两个参数都为NULL ,则返回NULL。
注意事项:string1和replace_with必须为同一数据类型,除非显式的使用TO_CHAR函数进行类型转换。
2.
DECODE()函数,它将输入数值与函数中的参数列表相比较,根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然,如果未能与任何一个实参序偶匹配成功,则函数也有默认的返回值。
语法结构如下:
decode (expression, search_1, result_1) decode (expression, search_1, result_1, search_2, result_2) decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n) decode (expression, search_1, result_1, default) decode (expression, search_1, result_1, search_2, result_2, default) decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)
比较表达式和搜索字,如果匹配,返回结果;如果不匹配,返回default值;如果未定义default值,则返回空值。
1. 创建一个学生成绩表。栏位有: 学生名, 科目名和分数
- create table student_score(
- name varchar2(30),
- subject varchar2(20),
- score number(4,1)
- );
2. 插入测试数据
- insert into student_score (name,subject,score)values('zhang san','Chinese',90);
- insert into student_score (name,subject,score)values('zhang san','Mathematics',80);
- insert into student_score (name,subject,score)values('zhang san','English',79);
3.
- select name,subject,decode(subject, 'Chinese',score,0) from student_score;
结果如下:
如果是中文课程的话, 显示分数, 其他课程分数为零。
这条SQL 看上去使用意义不大。
测试二:
- select name,sum(decode(subject, 'Chinese',score,0)) as CHINESE from student_score group by name;
统计中文课程的分数。看上去有点意义。
总体看来, decode 的使用看上去和case when 有点类似。如果只是用作以上两种状况,看上去意义不大。
- select name,sum(decode(subject, 'Chinese',score,0)) as CHINESE from student_score group by name;
- select name,sum(case when subject= 'Chinese'then score else 0 end) as CHINESE from student_score group by name;
使用的两句使用后的效果一样,看上去使用decode 多此一举。
行转列-有意义的使用
往以上table 再插入一些其他学生的成绩:
- insert into student_score (name,subject,score)values('li shi','Chinese',96);
- insert into student_score (name,subject,score)values('li shi','Mathematics',86);
- insert into student_score (name,subject,score)values('li shi','English',76);
- insert into student_score (name,subject,score)values('wang wu','Chinese',92);
- insert into student_score (name,subject,score)values('wang wu','Mathematics',82);
- insert into student_score (name,subject,score)values('wang wu','English',72);
使用以下SQL:
- select name,
- sum(decode(subject, 'Chinese', nvl(score, 0), 0)) "Chinese",
- sum(decode(subject, 'Mathematics', nvl(score, 0), 0)) "Mathematics",
- sum(decode(subject, 'English', nvl(score, 0), 0)) "English"
- from student_score
- group by name;
返回:
将行的数据转化为列, 是不是很有意义了。
使用case then 也可以达到相同的效果。
- select name,
- sum(case when subject='Chinese'
- then nvl(score,0)
- else 0
- end) "Chinese",
- sum(case when subject='Mathematics'
- then nvl(score,0)
- else 0
- end) "Mathematics",
- sum(case when subject='English'
- then nvl(score,0)
- else 0
- end) "English"
- from student_score
- group by name;
比如我要查询某班男生和女生的数量分别是多少?
通常我们这么写:
select count(*) from 表 where 性别 = 男;
select count(*) from 表 where 性别 = 女;
要想显示到一起还要union一下,太麻烦了
用decode呢,只需要一句话
select sum(decode(性别,男,1,0)),sum(decode(性别,女,1,0)) from 表
另:decode中可使用其他函数,如nvl函数或sign()函数等;
NVL(EXPR1,EXPR2)
若EXPR1是NULL,则返回EXPR2,否则返回EXPR1.
SELECT NAME,NVL(TO_CHAR(COMM),'NOT APPLICATION') FROM TABLE1;
如果用到decode函数中就是
select monthid,decode(nvl(sale,6000),6000,'NG','OK') from output
sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1,
如果取较小值就是
select monthid,decode(sign(sale-6000),-1,sale,6000) from output,即达到取较小值的目的。