oracle 11g 分支结构 decode case when 行转列

oracle 11g 分支结构 decode,case,when

1. 分支结构 decode case when

1.1 行转列

1.2 分支结构

IF [expr=search1]
then 
	result 
elif [expr=search2]
then 
	re2
else  
   default  
fi 

1.3 decode的用法的使用 10 Aa 20 Bb 30 Cc

select   empno,ename,deptno 
			decode(deptno,10,'Aa',20,'Bb',30,'Cc')deco(取得别名)
from   bank_emp;

1.4 简单case实现的是等职比较（表达式在when之前）

select   empno,ename,deptno 
			decode(deptno,10,'Aa',20,'Bb',30,'Cc')deco(取得别名)
			case  deptno   WHEN  10  THEN  'AAA'
									WHEN  20  THEN  'BBB'
									WHEN  30  THEN  'CCC'
					  ELSE  'DDD'
					  END  ca  
from   bank_emp;

select   empno,ename,deptno 
			decode(deptno,10,'Aa',20,'Bb',30,'Cc')deco(取得别名)
			case  deptno   WHEN  10  THEN  'AAA'
									WHEN  20  THEN  'BBB'
									WHEN  30  THEN  'CCC'
					  ELSE  'DDD'
					  END  ca  
		  case     when  deptno=10  then 'AAA'
		  			   when  deptno=20  then  'BBB'
		  			   when   deptno=30  then   'CCC'
		  		  ELSE  'DDDD'
		  		  END  ca2
from   bank_emp;

1.5 搜索case是实现不等值比较（表达式在when之后）

select  ename,sql  
			case   when  sql>=1000  then  sql+1
			case  when   sql>1000  and  sql<=2000  then sql+2
			case  when   sql>2000  and   sql<=3000  then   sql+3
			else  'sql+4'
			END  "up_sql"
from emp  order by sql;

1.6 按部门编号涨工资 10号涨工资10%，20号涨工资20%， 30号涨工资30%

1.6.1 用语句decode

select   ename,deptno,sql  
			decode(deptno,10,sql*1.1,sql*1.2,sql*1.3) new_sq
			  from   emp  order  by   deptno;

或

select  ename,deptno,sql   
			case  deptno   
					when   10  then  sql*1.1
					when  20  then   sql*1.2
			else   sql*1.3
			end  new_sql 
from  emp  order  by  deptno;

2.行转列

create  table  t4(id   int,name   varchar2(10),subject  varchar2(20),grade   number);
insert  into  t4 valuas(1,'ZORRO','语文',70)
insert into  t4  valuas(2,'ZORRO','数学',80)
insert  into  t4  valuas(3,'ZORRO','英语',75)
insert into   t4 valuas(4,'SEKER','语文',65)
insert into  t4  valuas(5,'SEKER','数学',70)
insert  into   t4 valuas(6,'SEKER','英语',60)
insert  into   t4 valuas(7,'BLUES','语文',60)
insert  into   t4  valuas(8,'BLUES','数学',90)
insert  into  t4  valuas(9,'PG','数学',80)
insert  into   t4 valuas(10,'PG','英语',90)
commit;
select  *   from   t4;

2.1 需要得到的结果按照语文，数学，英语的格式输出数据的形式

name   语文  数学  英语
seker    65     70      60
blues     60     90      0
pg         0        80     90  
zorro     70      80     75 
select   name 
			case  when  subject='语文'   then  grade  
			case  when  subject='数学'   then   grade 
			case  when   subject='英语'   then   grade 
from   t4;
或
select    name   
			max(case  when  subject='语文'  then   grand   else  0  end)   语文
			max(case  when   subject='数学'  then  grand   else 0   end)   数学
			max(case   when   subject='英语'   then    grand  else    0  end)  英语
from  t4  group  by   name;