--列出各个部门中工资高于本部门的平均工资的员工数和部门号,并按部门号排序
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)