先找出相同行,再剔除得出不符合相同行的求并集 WITH t AS ( select 20130903 state_date,1120 com_code,0044 client_code,50 amount,'cny'unit,30 sys_id,NULL channel_id from dual UNION select 20130903 state_date,1120 com_code,0057 client_code,10 amount,'cny'unit,30 sys_id,NULL channel_id from dual UNION select 20130903 state_date,1120 com_code,0044 client_code,100 amount,'cny'unit,20 sys_id,NULL channel_id from dual UNION select 20130903 state_date,1120 com_code,0045 client_code,136 amount,'cny'unit,20 sys_id,NULL channel_id from dual UNION select 20130903 state_date,1120 com_code,0057 client_code,10 amount,'cny'unit,1 sys_id,30 channel_id from dual UNION select 20130903 state_date,1120 com_code,0044 client_code,300 amount,'cny'unit,1 sys_id,20 channel_id from dual ) (select t1.state_date,t1.com_code,t1.client_code,t1.amount AS amount1,t1.sys_id,t2.amount AS amount2,t1.channel_id from t t1, t t2 WHERE nvl(t1.channel_id,0)=t2.sys_id AND t1.state_date=t2.state_date AND t1.com_code=t2.com_code AND t1.client_code=t2.client_code AND t1.unit=t2.unit )UNION (select state_date,com_code,client_code,amount AS amount1,sys_id,NULL AS amount2 ,channel_id from t WHERE channel_id IS NULL MINUS select t2.state_date,t2.com_code,t2.client_code,t2.amount AS amount1,t2.sys_id,NULL AS amount2 ,t2.channel_id from t t1,t t2 WHERE t1.channel_id IS NOT NULL AND t2.channel_id IS NULL AND t1.state_date=t2.state_date AND t1.com_code=t2.com_code AND t1.client_code=t2.client_code AND t1.unit=t2.unit AND t2.sys_id=t1.channel_id)
简单点说就是将要对比的数据由行记录转成列记录,并过滤掉已转的行记录。比如:在图1中第6行,其stat_date,com_code,client_code, unit与第3行一致,而其channel_id是20,这指向的是第3行的sys_id,也就是说这两行记录要做对比。 另外stat_date,com_code,client_code, unit, sys_id是做了唯一索引的,不会有重复
简单点说就是将要对比的数据由行记录转成列记录,并过滤掉已转的行记录。比如:在图1中第6行,其stat_date,com_code,client_code, unit与第3行一致,而其channel_id是20,这指向的是第3行的sys_id,也就是说这两行记录要做对比。 另外stat_date,com_code,client_code, unit, sys_id是做了唯一索引的,不会有重复 看3楼是否满足需求
简单点说就是将要对比的数据由行记录转成列记录,并过滤掉已转的行记录。比如:在图1中第6行,其stat_date,com_code,client_code, unit与第3行一致,而其channel_id是20,这指向的是第3行的sys_id,也就是说这两行记录要做对比。 另外stat_date,com_code,client_code, unit, sys_id是做了唯一索引的,不会有重复 看3楼是否满足需求厉害呀,已经满足需求了,非常感谢!
WITH t AS (
select 20130903 state_date,1120 com_code,0044 client_code,50 amount,'cny'unit,30 sys_id,NULL channel_id from dual UNION
select 20130903 state_date,1120 com_code,0057 client_code,10 amount,'cny'unit,30 sys_id,NULL channel_id from dual UNION
select 20130903 state_date,1120 com_code,0044 client_code,100 amount,'cny'unit,20 sys_id,NULL channel_id from dual UNION
select 20130903 state_date,1120 com_code,0045 client_code,136 amount,'cny'unit,20 sys_id,NULL channel_id from dual UNION
select 20130903 state_date,1120 com_code,0057 client_code,10 amount,'cny'unit,1 sys_id,30 channel_id from dual UNION
select 20130903 state_date,1120 com_code,0044 client_code,300 amount,'cny'unit,1 sys_id,20 channel_id from dual )
(select t1.state_date,t1.com_code,t1.client_code,t1.amount AS amount1,t1.sys_id,t2.amount AS amount2,t1.channel_id
from t t1, t t2
WHERE nvl(t1.channel_id,0)=t2.sys_id AND t1.state_date=t2.state_date
AND t1.com_code=t2.com_code AND t1.client_code=t2.client_code AND t1.unit=t2.unit )UNION
(select state_date,com_code,client_code,amount AS amount1,sys_id,NULL AS amount2 ,channel_id from t WHERE channel_id IS NULL
MINUS
select t2.state_date,t2.com_code,t2.client_code,t2.amount AS amount1,t2.sys_id,NULL AS amount2 ,t2.channel_id
from t t1,t t2 WHERE t1.channel_id IS NOT NULL AND t2.channel_id IS NULL
AND t1.state_date=t2.state_date
AND t1.com_code=t2.com_code AND t1.client_code=t2.client_code AND t1.unit=t2.unit AND t2.sys_id=t1.channel_id)
另外stat_date,com_code,client_code, unit, sys_id是做了唯一索引的,不会有重复
另外stat_date,com_code,client_code, unit, sys_id是做了唯一索引的,不会有重复
看3楼是否满足需求
另外stat_date,com_code,client_code, unit, sys_id是做了唯一索引的,不会有重复
看3楼是否满足需求厉害呀,已经满足需求了,非常感谢!