欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle特殊查询 行列倒转 分页

程序员文章站 2022-05-28 12:33:41
--查询工资最高的前三名 (分页的感觉)select * from(select * from emp order by sal desc) twhere rownum <=3--查询工资最高的4到6名 (分页-->排序 行号 选择三步)select * from (select t.*,rownu ......

--查询工资最高的前三名 (分页的感觉)
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