如下sql语句:
select a.treat_status p_state_id,
c.organ_id organ_id,
b.status_note state,
count(a.item_id) policynum
from t_policy_problem a,
t_treat_status b,
t_rpt_dim_organ dim,
t_contract_master c
where a.treat_status = b.treat_status and a.policy_id = c.policy_id and
dim.organ_id = c.organ_id and
c.organ_id in
(select m.organ_id
from t_company_organ m
start with m.organ_id = '1'
connect by m.parent_id = prior m.organ_id) and
(c.policy_type = 1 or c.policy_type = 3) and
a.insert_time >= to_date('2005-10-01', 'yyyy-mm-dd') and
a.insert_time < to_date('2005-10-31', 'yyyy-mm-dd') + 1
group by c.organ_id, b.status_note, a.treat_status
union
select a.treat_status p_state_id,
c.organ_id organ_id,
b.status_note state,
count(a.item_id) policynum
from t_policy_problem a,
t_treat_status b,
t_rpt_dim_organ dim,
t_policy c
where a.treat_status = b.treat_status and a.policy_id = c.policy_id and
dim.organ_id = c.organ_id and
c.organ_id in
(select m.organ_id
from t_company_organ m
start with m.organ_id = '1'
connect by m.parent_id = prior m.organ_id) and
(c.policy_type = 1 or c.policy_type = 3) and
a.insert_time >= to_date('2005-10-01', 'yyyy-mm-dd') and
a.insert_time < to_date('2005-10-31', 'yyyy-mm-dd') + 1
group by c.organ_id, b.status_note, a.treat_status;
查数据库结果为
1 1004 待发放 2
2 001 已发放 1
3 1004 已回复 1
4 HCM3 已回销 2
4 HCM3 已回销 11 请问如何将 1,2,3列值相同的结果加起来成为如下结果: 1 1004 待发放 2
2 001 已发放 1
3 1004 已回复 1
4 HCM3 已回销 13
select a.treat_status p_state_id,
c.organ_id organ_id,
b.status_note state,
count(a.item_id) policynum
from t_policy_problem a,
t_treat_status b,
t_rpt_dim_organ dim,
t_contract_master c
where a.treat_status = b.treat_status and a.policy_id = c.policy_id and
dim.organ_id = c.organ_id and
c.organ_id in
(select m.organ_id
from t_company_organ m
start with m.organ_id = '1'
connect by m.parent_id = prior m.organ_id) and
(c.policy_type = 1 or c.policy_type = 3) and
a.insert_time >= to_date('2005-10-01', 'yyyy-mm-dd') and
a.insert_time < to_date('2005-10-31', 'yyyy-mm-dd') + 1
group by c.organ_id, b.status_note, a.treat_status
union
select a.treat_status p_state_id,
c.organ_id organ_id,
b.status_note state,
count(a.item_id) policynum
from t_policy_problem a,
t_treat_status b,
t_rpt_dim_organ dim,
t_policy c
where a.treat_status = b.treat_status and a.policy_id = c.policy_id and
dim.organ_id = c.organ_id and
c.organ_id in
(select m.organ_id
from t_company_organ m
start with m.organ_id = '1'
connect by m.parent_id = prior m.organ_id) and
(c.policy_type = 1 or c.policy_type = 3) and
a.insert_time >= to_date('2005-10-01', 'yyyy-mm-dd') and
a.insert_time < to_date('2005-10-31', 'yyyy-mm-dd') + 1
group by c.organ_id, b.status_note, a.treat_status;
查数据库结果为
1 1004 待发放 2
2 001 已发放 1
3 1004 已回复 1
4 HCM3 已回销 2
4 HCM3 已回销 11 请问如何将 1,2,3列值相同的结果加起来成为如下结果: 1 1004 待发放 2
2 001 已发放 1
3 1004 已回复 1
4 HCM3 已回销 13
from
(
select a.treat_status p_state_id,
c.organ_id organ_id,
b.status_note state,
count(a.item_id) policynum
from t_policy_problem a,
t_treat_status b,
t_rpt_dim_organ dim,
t_contract_master c
where a.treat_status = b.treat_status and a.policy_id = c.policy_id and
dim.organ_id = c.organ_id and
c.organ_id in
(select m.organ_id
from t_company_organ m
start with m.organ_id = '1'
connect by m.parent_id = prior m.organ_id) and
(c.policy_type = 1 or c.policy_type = 3) and
a.insert_time >= to_date('2005-10-01', 'yyyy-mm-dd') and
a.insert_time < to_date('2005-10-31', 'yyyy-mm-dd') + 1
group by c.organ_id, b.status_note, a.treat_status
union
select a.treat_status p_state_id,
c.organ_id organ_id,
b.status_note state,
count(a.item_id) policynum
from t_policy_problem a,
t_treat_status b,
t_rpt_dim_organ dim,
t_policy c
where a.treat_status = b.treat_status and a.policy_id = c.policy_id and
dim.organ_id = c.organ_id and
c.organ_id in
(select m.organ_id
from t_company_organ m
start with m.organ_id = '1'
connect by m.parent_id = prior m.organ_id) and
(c.policy_type = 1 or c.policy_type = 3) and
a.insert_time >= to_date('2005-10-01', 'yyyy-mm-dd') and
a.insert_time < to_date('2005-10-31', 'yyyy-mm-dd') + 1
group by c.organ_id, b.status_note, a.treat_status
) r group by r.p_state_id,r.organ_id,r.state;
太长了
临时表的好处就是保证在你的session中是有效,结束session后,自动删除数据的。