select QTXX,sum(sl),sum(zl),sum(je),sum(money) from (select * from a,b where a.cid=b.cid(+)) tb group by QTXX;
select qttx,sl,zl,je,money from ((select qtxx,cid,sum(sl) as sl,sum(zl) as zl,sum(je) as je,0 from a group by qtxx,cid) a1,(select cid,sum(money) as money from b group by b) b1 ) where a1.cid=b1.cid(+);
可是,A表中的CID与B表中的KEY没有任何关系的啊!
select QTXX,sum(sl),sum(zl),sum(je),sum(money) from ( select a.*,b.money from a,b where a.cid=b.cid(+) ) group by QTXX;此外,如果A表、B表数据量非常庞大的情况下,建议select a.*,b.money from a,b where a.cid=b.cid(+) 语句,不使用外联结
12:40:55 SQL> select * from tba; ID CID SL ZL JE QT --------- --------- --------- --------- --------- -- 1 1 1 1.5 3000 HH 2 1 0 .5 1000 HH 3 3 10 20.5 30000 WW实际:90 12:41:03 SQL> select * from tbb; KEY CID MONEY --------- --------- --------- 1 1 10000 2 1 20000实际:50 12:41:07 SQL> select * from ( 12:41:12 2 select cid cid1,sum(sl) sl,sum(zl) zl,sum(je) je,qtxx from tba group by cid,qtxx) 12:41:12 3 a,(select cid,sum(money) from tbb group by cid) b 12:41:12 4 where a.cid1=b.cid(+); CID1 SL ZL JE QT CID SUM(MONEY) --------- --------- --------- --------- -- --------- ---------- 1 1 2 4000 HH 1 30000 3 10 20.5 30000 WW实际:80 12:41:12 SQL>
select qtxx,sum(sl),sum(zl),sum(je),sum(money) from a,b where a.id=b.id(+) and a.cid=b.cid(+) group by a.qtxx;
aoeiuv() 牛!! w_tsinghua() 的思路比较清晰! 学习ING!
a.id和b.key是没关系的? 没注意到,出丑了-_-|| 再来select qtxx,sum(sl),sum(zl),sum(je),avg(c.money) from a,(select sum(money) money, cid from b group by b.cid) c where a.cid=c.cid(+) group by a.qtxx试了一下,思路是w_清华的清楚,但是我这个效率比较高
from (select * from a,b where a.cid=b.cid(+)) tb
group by QTXX;
from (
select a.*,b.money from a,b where a.cid=b.cid(+)
)
group by QTXX;此外,如果A表、B表数据量非常庞大的情况下,建议select a.*,b.money from a,b where a.cid=b.cid(+) 语句,不使用外联结
--------- --------- --------- --------- --------- --
1 1 1 1.5 3000 HH
2 1 0 .5 1000 HH
3 3 10 20.5 30000 WW实际:90
12:41:03 SQL> select * from tbb; KEY CID MONEY
--------- --------- ---------
1 1 10000
2 1 20000实际:50
12:41:07 SQL> select * from (
12:41:12 2 select cid cid1,sum(sl) sl,sum(zl) zl,sum(je) je,qtxx from tba group by cid,qtxx)
12:41:12 3 a,(select cid,sum(money) from tbb group by cid) b
12:41:12 4 where a.cid1=b.cid(+); CID1 SL ZL JE QT CID SUM(MONEY)
--------- --------- --------- --------- -- --------- ----------
1 1 2 4000 HH 1 30000
3 10 20.5 30000 WW实际:80
12:41:12 SQL>
from a,b
where a.id=b.id(+) and a.cid=b.cid(+)
group by a.qtxx;
w_tsinghua() 的思路比较清晰!
学习ING!
再来select qtxx,sum(sl),sum(zl),sum(je),avg(c.money)
from a,(select sum(money) money, cid from b group by b.cid) c
where a.cid=c.cid(+)
group by a.qtxx试了一下,思路是w_清华的清楚,但是我这个效率比较高