There are three tables,
A01 personnel list (personnel)
B01 unit list some of the information (unit)
C01 wages list, some of the information (salary)

Now I query this rendering


Is a current detect all child unit of data, each unit is a unit of the superior,
such as 01 unit has subordinate units, 011 units and 012 units.
02 unit unit is 022 units and 023 units of

Query the number of the time
Select count (*) from a01 and b01 where a01. The unit code=b01. The code

Now I want to write a view,

Well, can't write - -, the view is this:
The CREATE OR REPLACE the VIEW VIEW_A01_B01_C01 AS
Select the unit name, count (*) as the number of pay
The From a01 b01, c01
Where a01. Id=c01. Id and a01. The unit code=b01. The code
Group by b01. The name of the entity, c01. Pay
The order by the unit code; ?

An afternoon did not solve, I wish to help, integral is not much, only hope to have the good help

CodePudding user response:

 
With t1 as 
(select '1' id, '1' department name, '0' pid from dual 
Union all 
Select '2' id 'authorities' name,' 1 'pid from dual 
Union all 
Select '3' id 'department 12' name, '1' pid from dual 
Union all 
Select '4' id, '2' department name, '0' pid from dual 
Union all 
Select '5' id 'department 21' name, '4' pid from dual 
Union all 
Select the '6' id, 22 'department' name, '5' pid from dual 
Union all 
Select the '7' id, '23 departments' name,' 5 'pid from dual 
) 
Select id, name from t1 
Start with pid='0' 
Connect by the prior id=pid 

Then, according to the department ID number and the number of salary summary

CodePudding user response:

With t as
(select '01' id, 2 PCNT, 7000 sal from dual
Union all
Select '011' id, 1200 from dual
Union all
Select '012' id, 2450 from dual
Union all
Select '02' id, 4130, 40 from dual
Union all
Select '021' id, 3760 from dual
Union all
Select '022' id, 2550 from dual
)
Select id, substr (id, 1, 2) a, the sum (PCNT), sum (sal), grouping_id (substr (id, 1, 2), id) gid,
Decode (grouping_id (substr (id, 1, 2), id), 0, id, 1, substr (id, 1, 2) | | 'and the following summary, 3,' all summary, null) sid
The from t group by rollup (substr (id, 1, 2), id)
/* having grouping_id (substr (id, 1, 2), id) & lt;> '3' */

CodePudding user response:

refer to the second floor jdsnhan response:

 
With t1 as 
(select '1' id, '1' department name, '0' pid from dual 
Union all 
Select '2' id 'authorities' name,' 1 'pid from dual 
Union all 
Select '3' id 'department 12' name, '1' pid from dual 
Union all 
Select '4' id, '2' department name, '0' pid from dual 
Union all 
Select '5' id 'department 21' name, '4' pid from dual 
Union all 
Select the '6' id, 22 'department' name, '5' pid from dual 
Union all 
Select the '7' id, '23 departments' name,' 5 'pid from dual 
) 
Select id, name from t1 
Start with pid='0' 
Connect by the prior id=pid 

Then, according to the department ID number and the number of wage summary

CodePudding user response:

SQL> Select deptno, job, sal from emp;

DEPTNO JOB SAL
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
20 at 800
30 SALESMAN 1600
30 SALESMAN 1250
20 MANAGER 2975
30 SALESMAN 1250
30 MANAGER 2850
10 MANAGER 2450
20 ANALYST 3000
10 PRESIDENT 5000
30 SALESMAN 1500
20 at 1100
30 at 950
20 ANALYST 3000
10 at 1300

SQL> With tt as (
2 the select deptno, count number of work (job), sum (sal) sector wages from emp group by cube (deptno, job))
3 the select * from tt where deptno is not null order by deptno, job desc;

DEPTNO number work sector wages
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
October 3, 8750
October 1, 2450
October 1, 5000
October 1, 1300
20 May 10875
20 2 1900
20 2 6000
20 1 2975
30 June 9400
30 April 5600
30 1 950
30 1 2850

Selected 12 lines,