select a.M, ((select nvl(N,0) from 表1 where 表1.M = a.M) + (select nvl(N,0) from 表2 where 表2.M = a.M) - (select nvl(N,0) from 表3 where 表3.M = a.M)) from (select M from 表1 union select M from 表2 union select M from 表3) a
select a.M, ((select sum(nvl(N,0)) from 表1 where 表1.M = a.M) + (select sum(nvl(N,0)) from 表2 where 表2.M = a.M) - (select sum(nvl(N,0)) from 表3 where 表3.M = a.M)) from (select M from 表1 union select M from 表2 union select M from 表3) a
select m,sum(n) from (select 表1.m, 表1.n from 表1 union select 表2.m, 表2.n from 表2 union select 表3.m, -表3.n from 表1) group by m;
SELECT * from (select M,(表1.N+表2.N-表3.N)as N from 表1,表2,表3 where 表1.M=表2.M=表3.M) group by M;
l2g32003(T2-X)同学,表1.M=表2.M=表3.M这种用法是9i之上版本的??我个人比较赞同lilygy5(我爱oracle)的语句: 不过我认为为了避免可能出现A 1,A 1同样的记录, 可以在union后加all 既union all
from (select M from 表1
union
select M from 表2
union
select M from 表3) a
from (select M from 表1
union
select M from 表2
union
select M from 表3) a
select 表2.m, 表2.n from 表2 union
select 表3.m, -表3.n from 表1)
group by m;
(select M,(表1.N+表2.N-表3.N)as N from 表1,表2,表3 where 表1.M=表2.M=表3.M)
group by M;
不过我认为为了避免可能出现A 1,A 1同样的记录,
可以在union后加all
既union all