ORACLE分组查询和统计等

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

注意这里查询的是“”分组“，因此这里查询的是变成一组为一个100%，查询的是一个部门中员工在本部分所占用的薪水比例

11分组查询出单一条件并分级（查询某一个部门的薪水的级别）注意rank()和row_number()的区别 rank是跳跃性并列(1.1.3.3.5) row_number(1.2.3.4.5)

select t.*,ROW_NUMBER() over(partition by t.deptno order by t.sal desc) rank from emp t

12“总”。。。。。。这个字眼一般使用group by（区分于over（partition by order by））

按部门分组查询部门的总薪水

select sum(t.sal),t.deptno from emp t group by t.deptno

13 总的基础上再次分组 group by + rollup

select sum(t.sal),t.deptno from emp t group by rollup (t.deptno)汇总后将总和进行求和

select sum(t.sal),t.job,t.deptno from emp t group by rollup (t.deptno,t.job)注意多个rollup其实只有第一个参数有效

14cube 连接在order by后面（代替ROLLUP） rollup升级版 全部分组

15grouping实现不用java代码就可以对oracle 查询出的null字段进行赋值 0 本身结果 1合计结果

select sum(t.sal),t.deptno,
(case
       when((grouping(t.job)=1 and grouping(t.deptno)=0)) then '部门小计'
       when((grouping(t.job)=1 and grouping(t.deptno)=1)) then '部门总计'
else t.job end) as
job from emp t group by rollup (t.deptno,t.job)

16分组后的字段累加(比如按照员工名称，根据月份分组，实现自1月份到12月份工资累加，即二月份是 1月 + 2月 。。)
select t.empno, t.ename, t.sal, sum(sal) over (partition by t.ename order by t.sal desc) from emp t

17分组最高值 最低值 平均值 使用 max() over(partition by order by)代替sum() 还可以用min() avg()

18select * from v$transaction 查看事务

19多层分组函数和子查询之间的冲突问题

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

select a.CREATOR, a.count_Sum, b.full_name, b.dept_code, b.area_code   from (select temp.c CREATOR, count(temp.c) as count_Sum           from (select t.ca,                        c.lv,                        instr(t.ca, ',', 1, c.lv) + 1,                        substr(t.ca,                               instr(t.ca, ',', 1, c.lv) + 1,                               instr(t.ca, ',', 1, c.lv + 1) -                               (instr(t.ca, ',', 1, c.lv) + 1)) AS c                   from (select ',' || checker || ',' AS ca,                                checker,                                LENGTH(checker),                                length(checker || ','),                                REPLACE(checker, ','),                                length(REPLACE(checker, ',')),                                nvl(length(REPLACE(checker, ',')), 0),                                length(checker || ',') -                                nvl(length(REPLACE(checker, ',')), 0) AS cnt                           FROM wm_time_info a                          where a.check_result != 1) t,                        　　 (select LEVEL lv from dual CONNECT BY LEVEL <= 100) c                  where c.lv <= t.cnt) temp          group by temp.c) a,        base_user b  where a.CREATOR = b.user_code

　　

　　外层查询和内层分组冲突

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

--select   a.CREATOR, a.count_Sum ,b.full_name,b.dept_code,b.area_code from (    select o.CREATOR,        count(o.CREATOR) as count_Sum,        FULLNAME,        DEPTCODE,        AREACODE   from (select temp.c      CREATOR,                b.full_name FULLNAME,                b.dept_code DEPTCODE,                b.area_code AREACODE,                work_date           from (select work_date,                        t.ca,                        c.lv,                        instr(t.ca, ',', 1, c.lv) + 1,                        substr(t.ca,                               instr(t.ca, ',', 1, c.lv) + 1,                               instr(t.ca, ',', 1, c.lv + 1) -                               (instr(t.ca, ',', 1, c.lv) + 1)) AS c                   from (                         ---                          select work_date,                                 ',' || checker || ',' AS ca,                                 checker,                                 LENGTH(checker),                                 length(checker || ','),                                 REPLACE(checker, ','),                                 length(REPLACE(checker, ',')),                                 nvl(length(REPLACE(checker, ',')), 0),                                 length(checker || ',') -                                 nvl(length(REPLACE(checker, ',')), 0) AS cnt                           FROM wm_time_info a                          where a.check_result != 1                         ---                                 ) t,                        　　 (select LEVEL lv from dual CONNECT BY LEVEL <= 100) c                  where c.lv <= t.cnt) temp,                base_user b          where temp.c = b.user_code) o --where work_date >='2016-01-10'    group by o.CREATOR, FULLNAME, DEPTCODE, AREACODE --) a ,base_user b where a.CREATOR = b.user_code

　　

　　20 注意 本条select中的分组和子查询都不可以作为函数的参数传入