这个应该每个表都有pronum字段吧,不然表跟表怎么连接?有的话应该是 select r.pronum,sum(r.num)-sum(f.num)+sum(t.num) from Ruku r,Fahuo f,Tuihuo t where r.pronum = f.pronum and f.pronum = t.pronum group by r.pronum
select r.pronum,sum(r.num)-nvl(sum(f.num),0)+nvl(sum(t.num),0) as num from (select pronum,SUM(num) as num from Ruku group by pronum) r left join (select pronum,SUM(num) as num from Fahuo group by pronum)f left join (select pronum,SUM(num) as num from Tuihuo group by pronum) group by r.pronum
改改,少打了on 条件select r.pronum,sum(r.num)-nvl(sum(f.num),0)+nvl(sum(t.num),0) as num from (select pronum,SUM(num) as num from Ruku group by pronum) r left join (select pronum,SUM(num) as num from Fahuo group by pronum)f on r.pronum=f.pronum left join (select pronum,SUM(num) as num from Tuihuo group by pronum)t on r.pronum=t.pronum group by r.pronum
把三个表的关联条件加上就可以了,如过这三个表都有pronum这列,可以先group by再计算。 SELECT t1.pronum, t1.num - NVL (t2.num, 0) + NVL (t3.num, 0) FROM ( SELECT r.pronum, SUM (r.num) num FROM Ruku r GROUP BY r.pronum) t1, ( SELECT f.pronum, SUM (f.num) num FROM Fahuo f GROUP BY f.pronum) t2, ( SELECT t.pronum, SUM (t.num) num FROM Tuihuo t GROUP BY t.pronum) t3 WHERE t1.pronum = t2.pronum(+) AND t1.pronum = t3.pronum(+)
select r.pronum,sum(r.num)-sum(f.num)+sum(t.num) from Ruku r,Fahuo f,Tuihuo t
where r.pronum = f.pronum and f.pronum = t.pronum
group by r.pronum
from (select pronum,SUM(num) as num from Ruku group by pronum) r
left join (select pronum,SUM(num) as num from Fahuo group by pronum)f
left join (select pronum,SUM(num) as num from Tuihuo group by pronum)
group by r.pronum
from (select pronum,SUM(num) as num from Ruku group by pronum) r
left join (select pronum,SUM(num) as num from Fahuo group by pronum)f on r.pronum=f.pronum
left join (select pronum,SUM(num) as num from Tuihuo group by pronum)t on r.pronum=t.pronum
group by r.pronum
SELECT t1.pronum, t1.num - NVL (t2.num, 0) + NVL (t3.num, 0)
FROM ( SELECT r.pronum, SUM (r.num) num
FROM Ruku r
GROUP BY r.pronum) t1,
( SELECT f.pronum, SUM (f.num) num
FROM Fahuo f
GROUP BY f.pronum) t2,
( SELECT t.pronum, SUM (t.num) num
FROM Tuihuo t
GROUP BY t.pronum) t3
WHERE t1.pronum = t2.pronum(+) AND t1.pronum = t3.pronum(+)