Oracle Group queries and statistics

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 can only be used < if you use > add Alias

2. Subquery references can only be referenced in the results of the query, such as the subquery does not detect flow_id, the outer layer is not used, the other layer cannot reference the inner t

3. Top three salary, inner layer to find out salary Order desc empty table outer layer using ROWNUM&LT;3

4.merge can implement existing data on update does not exist you just 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 isn't matched then

Insert () VALUES ()

5.start with connect by can query the data of a set of trees, note the condition of the last connect by (parent node = child node up query and down query)

Order can be ordered, can be added to two trees (or), you can also join the Where condition

SELECT * FROM emp

where ...

Start with EMPNC = 7369 or EMPNC = 7204 (Note that you cannot use and)

Connect by Prior mgr = Empno

ORDER BY ...

6 Share (percentage of total data that is queried for a particular data)

Select T.empno,t.ename,t.sal,
100*round (Sal/sum (SAL) over (), 5)
From EMP t

7 Continuous summation (same name divided into same group accumulation)

Select T.empno,t.ename,t.sal,
SUM (SAL) over (order by Sal)
From EMP t

8. Continuous summation with conditions (continuous summation by branch)

Select T.empno,t.ename,t.sal,t.deptno,
SUM (SAL) over (partition by T.deptno ORDER by Sal)
From EMP t

9. Division sum (take out the above)

Select T.empno,t.ename,t.sal,t.deptno,
SUM (SAL) over (partition by T.deptno)
From EMP t

10 group query share for payroll (% of total) take departmental groupings

Select T.empno,t.ename,t.sal,t.deptno,
100*round (Sal/sum (SAL) over (partition by T.deptno), 4)
From EMP t

Note that the query here is "" grouping, so the query here is turned into a group of 100%, the query is a department of employees in this part of the salary ratio

11 Grouping queries out a single condition and grading (query the level of salary for a department) note the difference between rank () and row_number () rank is the jumping juxtaposition (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 "Total" ... This word generally uses group by (from partition by order)

Query the department's total salary by department group

Select SUM (t.sal), T.deptno from EMP T GROUP by T.deptno

13 on a total basis again GROUP by + Rollup

Select SUM (t.sal), t.deptno from the EMP T GROUP by Rollup (T.DEPTNO) sum

Select SUM (t.sal), t.job,t.deptno from the EMP T GROUP by Rollup (t.deptno,t.job) Note multiple rollup in fact only the first parameter is valid

14cube connection after order by (instead of rollup) rollup all groups in the upgraded version

15grouping Implementation No Java code can be assigned to the null field of Oracle query 0 itself results 1 Total Results

Select SUM (t.sal), T.deptno,
(case
When ((Grouping (t.job) =1 and Grouping (T.DEPTNO) =0), then ' Department subtotal '
When ((Grouping (t.job) =1 and Grouping (T.DEPTNO) =1)] Then ' department total '
else T.job end) as
Job from EMP T GROUP by Rollup (T.deptno,t.job)

16 after the grouping of the field accumulation (such as according to the employee name, according to the month group, the implementation from January to December payroll accumulation, that is, February is January + February. )
Select T.empno, T.ename, T.sal, sum (SAL) over (partition by t.ename ORDER BY t.sal desc) from EMP t

17 The minimum value of the grouping highest value uses Max () over (partition by order by) to replace sum () and can also use min () Avg ()

18select * from V$transaction viewing transactions

19 Conflicting problems between multi-layer grouping functions and sub-queries

Select A.creator, A.count_sum, B.full_name,b.dept_code,b.area_code from (select Temp.c creator,count (temp.c) as Coun                  T_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 (len Gth (REPLACE (Checker, ', ')), 0), Length (Checker | |           　　 ', ')-NVL (the 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 <=) C where c.lv <= t.cnt) temp GROUP by TEMP.C) A, base _user b where A.creaTOR = B.user_code   

Outer query and intra-layer group conflicts

--select A.creator, A.count_sum, B.full_name,b.dept_code,b.area_code from (select O.creator,count (O.CREATOR) as Coun T_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.L  V) + 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 (len Gth (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 fro     M dual CONNECT by level <=) 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.C   Reator = B.user_code

20 note that neither the grouping nor the subquery in this select can be passed in as a function parameter

Oracle Group queries and statistics