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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
| select
org.Internal_Org_Id as Stat_Org_Id --机构编号
,(case when org.Internal_Org_Id = '999999888'
then '合计'
else org.Org_Name
end ) as Stat_Org_Name --机构代码
,(case when org.Internal_Org_Id = '999999888'
then 1
else 2
end) as ORDERID
,sum(data.jon_acct_num) as jon_acct_num
,sum(data.jon_cross_num) as jon_cross_num
,sum(data.jon_pay_num) as jon_pay_num
,sum(data.total_jon_num) as total_jon_num
,sum(data.des_acct_num) as des_acct_num
,sum(data.des_cross_num) as des_cross_num
,sum(data.des_pay_num) as des_pay_num
,sum(data.total_des_num) as total_des_num
,sum(data.accjon_acct_num) as accjon_acct_num
,sum(data.accjon_cross_num) as accjon_cross_num
,sum(data.accjon_pay_num) as accjon_pay_num
,sum(data.total_accjon_num) as total_accjon_num
,sum(data.accjon_acct_amt) as accjon_acct_amt
,sum(data.accjon_cross_amt) as accjon_cross_amt
,sum(data.accjon_acct1_amt) as accjon_acct1_amt
,sum(data.accjon_acct0_amt) as accjon_acct0_amt
,sum(data.accjon_pay_amt) as accjon_pay_amt
,sum(data.total_accjon_amt) as total_accjon_amt
from cimfbview.t98_int_org_app_rela_h org
left join
(
select
syt.Stat_Org_Id as Stat_Org_Id
,sum(syt.jon_acct_num) as jon_acct_num
,sum(syt.jon_cross_num) as jon_cross_num
,sum(syt.jon_pay_num) as jon_pay_num
,sum(syt.total_jon_num) as total_jon_num
,sum(syt.des_acct_num) as des_acct_num
,sum(syt.des_cross_num) as des_cross_num
,sum(syt.des_pay_num) as des_pay_num
,sum(syt.total_des_num) as total_des_num
,sum(syt.accjon_acct_num) as accjon_acct_num
,sum(syt.accjon_cross_num) as accjon_cross_num
,sum(syt.accjon_pay_num) as accjon_pay_num
,sum(syt.total_accjon_num) as total_accjon_num
,sum(syt.accjon_acct_amt) as accjon_acct_amt
,sum(syt.accjon_cross_amt) as accjon_cross_amt
,sum(syt.accjon_acct1_amt) as accjon_acct1_amt
,sum(syt.accjon_acct0_amt) as accjon_acct0_amt
,sum(syt.accjon_pay_amt) as accjon_pay_amt
,sum(syt.total_accjon_amt) as total_accjon_amt
from T98_SYT_ACCT_STAT_D_1 syt
where syt.Stat_Period_Cd = '1'
and syt.Summ_Date = to_date('20161001','yyyymmdd')
and syt.Stat_Org_Attr_Cd = '9'
and syt.Stat_Org_Id in (
select distinct(Internal_Org_Id) from t98_int_org_app_rela_h org where org.Internal_Org_Id = '999999888'
OR ORG.Parent_Int_Org_Id = '999999888'
or '999999888' =
(
case
when '1' = '1' and '2' in ('2','3') then nation_org_id
when '1' = '2' and '2' in ('3','4','5') then prov_org_id
when '1' = '3' and '2' in ('4','5') then city_org_id
when '1' = '4' and '2' = '5' then county_org_id
else Internal_Org_Id
end
)
and org.Int_Org_level_cd in ('1','2')
and org.Int_Org_Stru_Type_Cd='1'
and org.Int_Org_Type_Cd in ('01','07','10','05','99')
and org.Start_Dt <= to_date('20161001','yyyymmdd')
and org.End_Dt > to_date('20161001','yyyymmdd')
)
group by 1
) data
on org.Internal_Org_Id=data.Stat_Org_Id
where (org.Internal_Org_Id = '999999888'
OR ORG.Parent_Int_Org_Id = '999999888'
or '999999888' =
(
case
when '1' = '1' and '2' in ('2','3') then nation_org_id
when '1' = '2' and '2' in ('3','4','5') then prov_org_id
when '1' = '3' and '2' in ('4','5') then city_org_id
when '1' = '4' and '2' = '5' then county_org_id
else Internal_Org_Id
end
)
and org.Int_Org_level_cd in ('1','2')
)
and org.Int_Org_Stru_Type_Cd='1'
and org.Int_Org_Type_Cd in ('01','07','10','05','99')
and org.Start_Dt <= to_date('20161001','yyyymmdd')
and org.End_Dt > to_date('20161001','yyyymmdd')
group by 1,2,3;
GroupAggregate (cost=140463.35..140465.42 rows=18 width=622) (actual time=548.831..549.113 rows=37 loops=1)
Group Key: a.internal_org_id, (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN '合计'::character varying ELSE COALESCE
(org3.org_name, a.org_name) END), (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN 1 ELSE 2 END)
-> Sort (cost=140463.35..140463.40 rows=18 width=622) (actual time=548.789..548.817 rows=37 loops=1)
Sort Key: a.internal_org_id, (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN '合计'::character varying ELSE COA
LESCE(org3.org_name, a.org_name) END), (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN 1 ELSE 2 END)
Sort Method: quicksort Memory: 34kB
-> Nested Loop Left Join (cost=69906.79..140462.98 rows=18 width=622) (actual time=520.434..548.664 rows=37 loops=1)
Join Filter: (a.internal_org_id = data.stat_org_id)
Rows Removed by Join Filter: 669
-> Gather (cost=1000.42..68934.07 rows=18 width=76) (actual time=63.704..89.506 rows=37 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop Left Join (cost=0.42..67932.27 rows=8 width=76) (actual time=61.970..389.321 rows=12 loops=3
)
-> Parallel Seq Scan on t98_int_org_app_rela_h a (cost=0.00..67864.63 rows=8 width=51) (actual time=6
1.885..388.864 rows=12 loops=3)
Filter: ((int_org_stru_type_cd = '1'::bpchar) AND (start_dt <= to_date('20161001'::text, 'yyyymmd
d'::text)) AND (end_dt > to_date('20161001'::text, 'yyyymmdd'::text)) AND (int_org_type_cd = ANY ('{01,07,10,05,99}'::bpchar[])) A
ND ((internal_org_id = '999999888'::bpchar) OR (parent_int_org_id = '999999888'::bpchar) OR (('999999888'::bpchar = CASE WHEN ('2'
::text = ANY ('{2,3}'::text[])) THEN nation_org_id ELSE internal_org_id END) AND (int_org_level_cd = ANY ('{1,2}'::bpchar[])))))
Rows Removed by Filter: 549182
-> Index Scan using idx_3 on t04_sys_organization org3 (cost=0.42..8.44 rows=1 width=45) (actual time
=0.027..0.029 rows=1 loops=37)
Index Cond: ((a.new_org_id = internal_org_id) AND (sys_id = 'S20'::character(3)))
Filter: (province_cd = 71)
-> Materialize (cost=68906.37..71450.71 rows=292 width=586) (actual time=12.309..12.389 rows=19 loops=37)
-> Subquery Scan on data (cost=68906.37..71449.25 rows=292 width=586) (actual time=455.410..457.807 rows=40
loops=1)
-> GroupAggregate (cost=68906.37..71446.33 rows=292 width=586) (actual time=455.407..457.739 rows=40
loops=1)
Group Key: syt.stat_org_id
-> Nested Loop (cost=68906.37..71416.40 rows=292 width=100) (actual time=455.322..457.390 rows=
41 loops=1)
-> Unique (cost=68905.79..68907.25 rows=291 width=10) (actual time=455.259..456.424 rows=
44 loops=1)
-> Sort (cost=68905.79..68906.52 rows=292 width=10) (actual time=455.255..455.810 r
ows=710 loops=1)
Sort Key: org.internal_org_id
Sort Method: quicksort Memory: 58kB
-> Gather (cost=1000.00..68893.83 rows=292 width=10) (actual time=0.683..453.
986 rows=725 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on t98_int_org_app_rela_h org (cost=0.00..67864.63
rows=122 width=10) (actual time=2.719..448.507 rows=242 loops=3)
Filter: ((internal_org_id = '999999888'::bpchar) OR (parent_int_org
_id = '999999888'::bpchar) OR (('999999888'::bpchar = CASE WHEN ('2'::text = ANY ('{2,3}'::text[])) THEN nation_org_id ELSE intern
al_org_id END) AND (int_org_level_cd = ANY ('{1,2}'::bpchar[])) AND (int_org_stru_type_cd = '1'::bpchar) AND (int_org_type_cd = AN
Y ('{01,07,10,05,99}'::bpchar[])) AND (start_dt <= to_date('20161001'::text, 'yyyymmdd'::text)) AND (end_dt > to_date('20161001'::
text, 'yyyymmdd'::text))))
Rows Removed by Filter: 548953
-> Index Scan using idx_1 on t98_syt_acct_stat_d_1 syt (cost=0.58..8.60 rows=1 width=100)
(actual time=0.016..0.017 rows=1 loops=44)
Index Cond: ((stat_org_id = org.internal_org_id) AND (summ_date = to_date('20161001':
:text, 'yyyymmdd'::text)) AND (stat_period_cd = '1'::bpchar) AND (stat_org_attr_cd = '9'::bpchar))
Planning time: 2.997 ms
Execution time: 565.724 ms
|