Oracle-decode函数

比如我要查询某班男生和女生的数量分别是多少?

select sum(decode(性别，男，1，0）)，sum(decode(性别，女，1，0）) from 表

例如：

select sum(decode(siteno,'LT',1,0)),sum(decode(siteno,'SZ',1,0)) from facd605;

select sum(case siteno when 'LT' then 1 else 0 end),sum(case siteno when 'SZ' then 1 else 0 end) from facd605;

----------------------------------------------------------------------------------------------------------------------------------------------------------

将每一个工资等级替换成具体的文字信息，例如：

1 替换成 第五等工资、2 替换成 第四等工资、3 替换成 第三等工资，依次类推 --> 依靠DECODE()实现

SELECT e.empno, e.ename, e.sal, d.dname
DECODE(s.grade,1,’第五等工资’,2,’第四等工资’,3,’第三等工资’,4,’第二等工资’,5,’第一等工资’) gradeinfo
FROM emp e, dept d, salgrade s
WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;

----------------------------------------------------------------------------------------------------------------------------------------------------------

行转列：

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);

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;