含义解释: decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,默认值) 该函数的含义如下: IF 条件=值1 THEN RETURN(翻译值1) ELSIF 条件=值2 THEN RETURN(翻译值2) ...... ELSIF 条件=值n THEN RETURN(翻译值n) ELSE RETURN(缺省值) END IF decode(字段或字段的运算,值1,值2,值3) 这个函数运行的结果是,当字段或字段的运算的值等于值1时,该函数返回值2,否则返回值3 当然值1,值2,值3也可以是表达式,这个函数使得某些sql语句简单了许多 create table table_subject ( "id" int primary key not null, subject_name varchar2(20) not null, score int not null ) create sequence subject_seq ; insert into table_subject values(subject_seq.nextval,'语文',98); insert into table_subject values(subject_seq.nextval,'英语',67); insert into table_subject values(subject_seq.nextval,'数学',90); insert into table_subject values(subject_seq.nextval,'数学',89); insert into table_subject values(subject_seq.nextval,'语文',97); insert into table_subject values(subject_seq.nextval,'语文',96); insert into table_subject values(subject_seq.nextval,'语文',95); insert into table_subject values(subject_seq.nextval,'英语',78); select * from table_subject group by subject_name; --按照中文指定字段排序(语文、数学、英语) --其中1,2,3 代表显示的顺序 select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2 ,'英语',3);
select * from table_subject order by decode(subject_name, '语文', 3, '数学', 2 ,'英语',1); -- 一条语句统计语数英的数量 select sum(decode(subject_name,'语文',1,0)) as "语文", sum(decode(subject_name,'数学',1,0)) as "数学", sum(decode(subject_name,'英语',1,0)) as "英语" from table_subject;
Oracle 中 decode 函数用法
最新推荐文章于 2023-09-04 20:38:38 发布