Oracle 中 decode 函数用法

含义解释：
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;