select * from(select t.*,rownum rn from(select * from scott.emp order by empno desc)t where rownum<10) where rn>=1;
select * from(select a.*,rownum rn from (select * from scott.emp) a where rownum<=10) where rn>=5 ;
--left outer join:
左连接
select a.*,b.* from scott.emp a left outer join scott.dept b on a.deptno=b.deptno ;
--right outer join:
右连接
select a.*,b.* from scott.emp a right outer join scott.dept b on a.deptno=b.deptno ;
--inner join
select a.*,b.* from scott.emp a inner join scott.dept b on a.deptno=b.deptno ;
--full join
select a.*,b.* from scott.emp a full join scott.dept b on a.deptno=b.deptno ;
select a.*,b.* from scott.emp a,scott.dept b where a.deptno(+)=b.deptno ;
select distinct ename,sal from scott.emp a group by sal having ;
select * from scott.dept ;
select * from scott.emp ;
--case when then end (
交叉报表
)
select ename,sal,case deptno when 10 then '会计部' when 20 then '研究部' when 30 then '销售部' else '其他部门' end 部门
from scott.emp ;
结果:
select ename,sal,case when sal>0 and sal<1500 then '一级工资' when sal>=1500 and sal<3000 then '二级工资' when
sal>=3000 and sal<4500 then '三级工资' else '四级工资' end 工资等级 from scott.emp order by sal desc ;
结果:
--
交叉报表是使用分组函数与
case
结构一起实现
select 姓名,sum(case 课程 when '数学' then 分数 end)数学,sum(case 课程 when '历史' then 分数 end)历史 from 学生 group
by 姓名 ;
--decode
函数
select 姓名,sum(decode(课程,'数学',分数,null))数学,sum(decode(课程,'语文',分数,null))语文,sum(decode(课程,'历史','分
数',null))历史 from 学生 group by 姓名 ;
--level
。。。。
connect by(
层次查询
)
select level,emp.* from scott.emp connect by prior empno = mgr order by level ;
结果: