表1：機構表(機構Id，機構名稱，父級機構Id , 機構等級)
company_id , company_name , parent_company_id , company_level
表2：工單表(工單id, 工單所屬市級機構id,工單所屬縣級機構id,工單所屬門店機構id,工單狀態[已處理、未處理] )
order_id , company_id_1 , company_id_2 , company_id_3 , order_state
輸入某個機構ID，統計出該機構及其下屬機構的工單處理情況。(機構id，機構名稱，總工單數量，未處理工單數量，已處理工單數量)：
company_id , company_name , sum_order , not_read_order , read_order
比如：輸入洛陽市機構ID(7302),則統計出：
company_id , company_name , sum_order , not_read_order , read_order
7302 , 洛陽市 100 50 50
730201 洛陽市孟津縣 40 10 30
730202 洛陽市汝陽縣 30 11 19
...
...
...
uj5u.com熱心網友回復：
兩張表的關系是什么你得給出來吧uj5u.com熱心網友回復：
僅供參考create table company(company_id int, company_name varchar2(500) , parent_company_id int, company_level int);
insert into company
select 1,'洛陽市',0,1 from dual
union all
select 2,'洛陽市孟津縣',1,2 from dual
union all
select 3,'洛陽市汝陽縣',1,2 from dual
union all
select 4,'洛陽市汝陽縣小李門店',2,3 from dual;
create table orders(
order_id int , company_id_1 int, company_id_2 int, company_id_3 int, order_state int);
insert into 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.company_id,
a.company_name,
count(1) as 總工單數量,
sum(case when a.order_state=0 then 1 else 0 end) as 未處理工單數量,
sum(case when a.order_state=1 then 1 else 0 end) as 已處理工單數量
from (
select a.*,
--o.order_state,
--o1.order_state,
--o2.order_state,
nvl(o.order_state,nvl(o1.order_state,o2.order_state)) as order_state
from (
select t.*,level
from company t
start with t.company_id=1
connect by prior t.company_id=t.parent_company_id
) a
left join orders o
on a.company_id=o.company_id_1
left join orders o1
on a.company_id=o1.company_id_2
left join orders o2
on a.company_id=o2.company_id_3
) a
group by a.company_id,
a.company_name
order by a.company_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/66575.html
標籤：開發