17,086
社区成员
发帖
与我相关
我的任务
分享
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)