oracle行转列，case的用法，表的合并

--列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序
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,b1
where (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,b1
where (t1.name=b1.name(+)) 
union
select b1.name,t1.age,b1.score from t1,b1 
where (t1.name(+)=b1.name)