Table 1: organization table (Id, name, the parent organization Id, institution level)
Company_id, company_name, parent_company_id company_level

Table 2: work order list (order id, order id belongs to the municipal agencies, institutions at the county level id, work order belong to the repair order belongs to store id, work order status/processed and unprocessed)
Order_id, company_id_1 company_id_2 company_id_3, order_state


Input ID in an organisation, the statistics of the agency and its subordinate institutions work order, (ID, name, total number of single, pending order quantity, has to deal with the repair order quantity) :
Company_id, company_name, sum_order not_read_order, read_order


Such as: input of luoyang agency ID (7302), the statistical data:
Company_id, company_name, sum_order not_read_order, read_order
7302, luoyang 100 50 fifty
Luoyang 730201 mengjin 40 10 30
Luoyang ruyang 30 November 19 730202
.
.
.

CodePudding user response:

What is the relationship between the two tables you have to give out

CodePudding user response:

For reference only

The create table company (company_id int, company_name varchar2 (500), parent_company_id int, company_level int);

Insert into company
Select 1, 'the city of luoyang, 0, 1 from dual
Union all
Select 2, 'luoyang mengjin, 1, 2 from dual
Union all
Select 3, 'luoyang ruyang, 1, 2 from dual
Union all
Select 4, 'luoyang ruyang county xiao li stores', 2, 3 from dual;


The create table orders (
Order_id int, company_id_1 int, company_id_2 int, company_id_3 int, order_state int);



Insert into the orders
Select 1001,1,2,4,0 from dual
Union all
Select 1002,1,2,4,1 from dual
Union all
Select 1003,1,3,0,1 from dual
Union all
Select 1004,1,3,0,0 from dual;



Select
A.com pany_id,
A.com pany_name,
Count (1) as general single number,
The sum (case when a.o rder_state=0 then 1 else 0 end) as pending order quantity,
Sum (case when a.o rder_state=1 then 1 else 0 end) as processed work order number
The from (

Select a. *,
- o.o rder_state,
-- o1. Order_state,
-- o2. Order_state,
NVL (o.o rder_state NVL (o1. Order_state, o2. Order_state)) as order_state
The from (
Select t. *, level
The from company t
Start with t.com pany_id=1
Connect by the prior t.com pany_id=t.p arent_company_id
) a
Left the join the orders o
On a.company_id=o.com pany_id_1

Left the join the orders o1
On a.company_id=o1.com pany_id_2

Left the join the orders o2
On a.company_id=o2.com pany_id_3

) a
Group by a.com pany_id,
A.com pany_name
The order by a.com pany_id