问一个SQL逻辑的问题，sql执行和预期不符合，SQL大拿请帮忙哦！！

[太白山鹰]

目标：
p1 关联 p2 表，并汇总 p2的FIN_AMT字段
p1关联p3表，并汇总p3的SUM_AGENT_FEE字段
预想通过一条sql实现：
SELECT P1.ASSET_ROW_ID
,P1.LOCAL_NET_ID
,SUM(COALESCE(P2.FIN_AMT,0.0))*0.01 SUM_FIN_AMT
,SUM(COALESCE(P3.SUM_AGENT_FEE,0.0)) SUM_AGENT_FEE
FROM HEMSS.HEMS_A_OUT_Abnormal_Model_Z P1
LEFT JOIN BICVIEW_Z.FIN_ASSET_REV_MON_A P2
ON P1.ASSET_ROW_ID=P2.ASSET_ROW_ID
AND P2.BIL_MONTH >= to_char(TIMESTAMP_ISO(P1.serv_start_dt),'YYYYMM') AND P3.BILL_MONTH <= to_char(TIMESTAMP_ISO(P1.OUT_NET_DT),'YYYYMM')
LEFT JOIN SESSION.MKT_ASSET_REWARD_M_Z_A P3
ON P1.ASSET_ROW_ID=P3.ASSET_ROW_ID
AND P3.BILL_MONTH >= to_char(TIMESTAMP_ISO(P1.serv_start_dt),'YYYYMM') AND P3.BILL_MONTH <= to_char(TIMESTAMP_ISO(P1.OUT_NET_DT),'YYYYMM')
WHERE P1.BILL_MONTH = '201410'
GROUP BY P1.ASSET_ROW_ID
,P1.LOCAL_NET_ID
但是得到的结果不对，然后我发现分开写：
SELECT P1.ASSET_ROW_ID
,P1.LOCAL_NET_ID
,SUM(COALESCE(P2.FIN_AMT,0.0))*0.01 SUM_FIN_AMT
FROM HEMSS.HEMS_A_OUT_Abnormal_Model_Z P1
LEFT JOIN BICVIEW_Z.FIN_ASSET_REV_MON_A P2
ON P1.ASSET_ROW_ID=P2.ASSET_ROW_ID
AND P2.BIL_MONTH >= to_char(TIMESTAMP_ISO(P1.serv_start_dt),'YYYYMM') AND P3.BILL_MONTH <= to_char(TIMESTAMP_ISO(P1.OUT_NET_DT),'YYYYMM')
WHERE P1.BILL_MONTH = '201410'
GROUP BY P1.ASSET_ROW_ID
,P1.LOCAL_NET_ID;

SELECT P1.ASSET_ROW_ID
,P1.LOCAL_NET_ID
,SUM(COALESCE(P3.SUM_AGENT_FEE,0.0)) SUM_AGENT_FEE
FROM HEMSS.HEMS_A_OUT_Abnormal_Model_Z P1
LEFT JOIN SESSION.MKT_ASSET_REWARD_M_Z_A P3
ON P1.ASSET_ROW_ID=P3.ASSET_ROW_ID
AND P3.BILL_MONTH >= to_char(TIMESTAMP_ISO(P1.serv_start_dt),'YYYYMM') AND P3.BILL_MONTH <= to_char(TIMESTAMP_ISO(P1.OUT_NET_DT),'YYYYMM')
WHERE P1.BILL_MONTH = '201410'
GROUP BY P1.ASSET_ROW_ID
,P1.LOCAL_NET_ID

这两天语句是分开汇总，结果是对的，请问这是什么原因？
~~想了半天不知道，请大神指教~

[bw555]

p2和p3不是一一对应的 举个例子比较好理解 p1就1条记录，p2有两条记录与之对应，p3有3条记录与之对应 3个表一起关联，则得到6条数据，则汇总结果是这6条数据的汇总 分别汇总时 p1与p2关联得到2条记录，汇总结果是这两条的汇总 p1与p3关联得到3条记录，汇总结果是这3条的汇总

[wuxiaobo_2014]

drop table t1;
drop table t2;
drop table t3;
create table t1 (id number,name varchar2(20));
create table t2 (id number,m1 number);
create table t3(id number,m2 number);
insert into t1 select 1,'zhangsan' from dual;
insert into t1 select 2,'lisi' from dual;
insert into t2 select 1,10 from dual;
insert into t2 select 1,20 from dual;

insert into t3 select 1,10 from dual;
insert into t3 select 1,20 from dual;
insert into t3 select 1,30 from dual;

SELECT * FROM t3;

select * from t1,t2,t3
where t1.id=t2.id
and t1.id=t3.id

解析出来自己看，就是 join 没有理解清楚。

[太白山鹰]

引用 1 楼 bw555 的回复:

p2和p3不是一一对应的 举个例子比较好理解 p1就1条记录，p2有两条记录与之对应，p3有3条记录与之对应 3个表一起关联，则得到6条数据，则汇总结果是这6条数据的汇总 分别汇总时 p1与p2关联得到2条记录，汇总结果是这两条的汇总 p1与p3关联得到3条记录，汇总结果是这3条的汇总

是的P2和P3是不一一对应的，要写成一条语句怎么写？知道不

[bw555]

先分别汇总再关联

with T1 AS (
SELECT P1.ASSET_ROW_ID
       ,P1.LOCAL_NET_ID
       ,SUM(COALESCE(P2.FIN_AMT,0.0))*0.01 	SUM_FIN_AMT 
FROM HEMSS.HEMS_A_OUT_Abnormal_Model_Z P1
LEFT JOIN BICVIEW_Z.FIN_ASSET_REV_MON_A P2
ON P1.ASSET_ROW_ID=P2.ASSET_ROW_ID
AND P2.BIL_MONTH >= to_char(TIMESTAMP_ISO(P1.serv_start_dt),'YYYYMM') AND P3.BILL_MONTH <= to_char(TIMESTAMP_ISO(P1.OUT_NET_DT),'YYYYMM')
WHERE P1.BILL_MONTH = '201410'
GROUP BY P1.ASSET_ROW_ID
         ,P1.LOCAL_NET_ID
),T2 AS (
SELECT P1.ASSET_ROW_ID
       ,P1.LOCAL_NET_ID
      ,SUM(COALESCE(P3.SUM_AGENT_FEE,0.0))  SUM_AGENT_FEE 
FROM HEMSS.HEMS_A_OUT_Abnormal_Model_Z P1
LEFT JOIN SESSION.MKT_ASSET_REWARD_M_Z_A P3
ON P1.ASSET_ROW_ID=P3.ASSET_ROW_ID
AND P3.BILL_MONTH >= to_char(TIMESTAMP_ISO(P1.serv_start_dt),'YYYYMM') AND P3.BILL_MONTH <= to_char(TIMESTAMP_ISO(P1.OUT_NET_DT),'YYYYMM')
WHERE P1.BILL_MONTH = '201410'
GROUP BY P1.ASSET_ROW_ID
         ,P1.LOCAL_NET_ID
)
SELECT T1.ASSET_ROW_ID,T1.LOCAL_NET_ID,T1.SUM_FIN_AMT ,T2.SUM_AGENT_FEE 
FROM T1,T2
WHERE T1.ASSET_ROW_ID=T2.ASSET_ROW_ID
         AND T1.LOCAL_NET_ID=T2.LOCAL_NET_ID