–查询工资最高的前三名 (分页的感觉)
select * from
(select * from emp order by sal desc) t
where rownum <=3
–查询工资最高的4到6名 (分页–>排序 行号 选择三步)
select *
from (select t.*,rownum rn from (select * from emp order by sal desc) t) m where m.rn >= 4 and m.rn<=6
select *
from (select t.*,rownum rn from (select * from emp order by sal desc) t) m where m.rn between 4 and 6
select *
from (select e.*,row_number() over(order by e.sal desc) rn from emp e) t
where t.rn between 4 and 6
–查询每年入职的员工个数
select count(*), to_char(hiredate,’yyyy’) from emp group by to_char(hiredate,’yyyy’)
–把上面查询的表行列倒转
select count(*), to_char(hiredate,’yyyy’) from emp group by to_char(hiredate,’yyyy’)
select
sum(num) “Total”,
avg(decode(hireyear,’1980′,num)) “1980”,
sum(decode(hireyear,’1981′,num)) “1981”,
max(decode(hireyear,’1982′,num)) “1982”,
min(decode(hireyear,’1987′,num)) “1987”
from
(select count(*) num, to_char(hiredate,’yyyy’) hireyear from emp group by to_char(hiredate,’yyyy’)) t
–交集 两个集合共同的元素
select * from emp where deptno=20
intersect
select * from emp where sal>2000
–并集 两个集合所有的元素
–不去重
select * from emp where deptno=20
union all
select * from emp where sal>2000
–去重
select * from emp where deptno=20
union
select * from emp where sal>2000
–差集 A有B没有的元素
select * from emp where deptno=20
Minus
select * from emp where sal>2000