select flow_id,rw from (select t.flow_id ,rownum as rw from apex_030200.wwv_flow_list_templates t) where rw >= 5
1.rownum只能用加别名
2.子查询引用只能在查询出的结果中引用,比如子查询没有查出flow_id,外层不能用,另外外层不能引用内层的t
3.薪水前三名,内层查出薪水 order desc的虚表外层使用rownum<3
4.merge可以实现存在数据就update不存在你就insert
merge into product a
using (select 1717 product_id, '002' req_no from dual b)
on (a.product_id = b.product.id and a.req_no = b.req.no)
when matched then
update set product name = ''.....................
when not matched then
insert () values ()
5.start with connect by 可以查询出一组树的数据,注意最后connect by的条件(父节点=子节点 向上查询 反之向下查询)
可以order排序,可以加入两棵树(or),也可以加入where条件
select * from emp
where......
start with empnc = 7369 or empnc = 7204(注意不能用and )
connect by prior mgr = empno
order by ...
6 份额(查询某数据占有总数据的百分比)
select t.empno,t.ename,t.sal,
100*round(sal/sum(sal) over(),5)
from emp t
7 连续求和(同名分为同组 累加)
select t.empno,t.ename,t.sal,
sum(sal) over(order by sal)
from emp t
8.带条件的连续求和(分部门连续求和)
select t.empno,t.ename,t.sal,t.deptno,
sum(sal) over(partition by t.deptno order by sal)
from emp t
9.分部门总和(取出orderby)
select t.empno,t.ename,t.sal,t.deptno,
sum(sal) over(partition by t.deptno)
from emp t
10工资的分组查询份额(总数的百分比)带上部门分组
select t.empno,t.ename,t.sal,t.deptno,
100*round(sal/sum(sal) over (partition by t.deptno),4)
from emp t
注意这里查询的是“”分