oracle行轉列，case的用法，表的合并__oracle

--列出各個部門中工資高於本部門的平均工資的員工數和部門號，並按部門號排序select * from emp e1,(select round(avg(sal),2) sal,deptno from emp group by deptno) e2 where e1.sal>e2.sal and e1.deptno=e2.deptno order by e1.deptno asc;

建立test表

--行轉列的測試create table test(id number primary key,name varchar2(20),total number,quarter varchar2(20))create sequence test_seq increment by 1 start with 1;insert into test(id,name,total,quarter) values(test_seq.nextval,'乳酪'，50，'第一季度');insert into test(id,name,total,quarter) values(test_seq.nextval,'乳酪'，60，'第二季度');insert into test(id,name,total,quarter) values(test_seq.nextval,'啤酒'，50，'第二季度');insert into test(id,name,total,quarter) values(test_seq.nextval,'啤酒'，80，'第四季度');commit;

select * from test;

select name,sum(decode(quarter,'第一季度',total,0)) as 第一季度銷售額,sum(decode(quarter,'第二季度',total,0)) as 第二季度銷售額,sum(decode(quarter,'第三季度',total,0)) as 第三季度銷售額,sum(decode(quarter,'第四季度',total,0)) as 第四季度銷售額 from test group by name;

case的用法

select name,(case when total<60 then '不合格'when total>=60 and total<80 then '合格'when total>=80 then '優秀' end) as 等級 from test;

oracle表的合并

create table t1(id number primary key,name varchar2(20),age number);insert into t1(id,name,age) values(1,'A',18);insert into t1(id,name,age) values(2,'B',20);insert into t1(id,name,age) values(3,'C',22);commit;

create table b1(id number primary key,name varchar2(20),score number);insert into b1(id,name,score) values(1,'A',78);insert into b1(id,name,score) values(2,'B',85);insert into b1(id,name,score) values(3,'D',90);commit;

合并查詢

select t1.name,t1.age,b1.score from t1,b1where (t1.name=b1.name(+)) 

select b1.name,t1.age,b1.score from t1,b1 where (t1.name(+)=b1.name)

最後結果

select t1.name,t1.age,b1.score from t1,b1where (t1.name=b1.name(+)) unionselect b1.name,t1.age,b1.score from t1,b1 where (t1.name(+)=b1.name)