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 没有理解清楚。
先分别汇总再关联 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
举个例子比较好理解
p1就1条记录,p2有两条记录与之对应,p3有3条记录与之对应
3个表一起关联,则得到6条数据,则汇总结果是这6条数据的汇总分别汇总时
p1与p2关联得到2条记录,汇总结果是这两条的汇总
p1与p3关联得到3条记录,汇总结果是这3条的汇总
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 没有理解清楚。
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