A表中有个blance值
acct_id
blance
B表结构
serv_id
acct_id
C表结构
serv_id
charge
D表结构
serv_id
name
现在我要用一条SQL语句得到 当name是XXX的c.charge-a.blance的值,并用acct_id分组看c.charge-a.blance的结果。select t1.serv_id,sum1-sum2 from (
select b.serv_id,sum(a.balance) as sum1 from a,d,b where d.serv_id=b.serv_id and a.acct_id=b.acct_id and d.name='XXXX' group by b.serv_id) t1
,
(select c.serv_id,sum(c.charge) as sum2 from c,d where c.serv_id=d.serv_id and d.name='XXXX' group by c.serv_id )t2
where t1.serv_id=t2.serv_id根据这样的语句我虽然得到了结果,可是我的数据量太大,现需优化,哪怕是plsql存储过程也行,着急啊
acct_id
blance
B表结构
serv_id
acct_id
C表结构
serv_id
charge
D表结构
serv_id
name
现在我要用一条SQL语句得到 当name是XXX的c.charge-a.blance的值,并用acct_id分组看c.charge-a.blance的结果。select t1.serv_id,sum1-sum2 from (
select b.serv_id,sum(a.balance) as sum1 from a,d,b where d.serv_id=b.serv_id and a.acct_id=b.acct_id and d.name='XXXX' group by b.serv_id) t1
,
(select c.serv_id,sum(c.charge) as sum2 from c,d where c.serv_id=d.serv_id and d.name='XXXX' group by c.serv_id )t2
where t1.serv_id=t2.serv_id根据这样的语句我虽然得到了结果,可是我的数据量太大,现需优化,哪怕是plsql存储过程也行,着急啊
where b.acct_id = a.acct_id
and b.serv_id = c.serv_id
and b.serv_id = d.serv_id
and d.name = 'XXX'
用acct_id分组,是不是应该将select 语句中的name替换为a.acct_id,如下:
select a.acct_id,sum(c.change-a.blance) from a,b,c,d
where b.acct_id = a.acct_id
and b.serv_id = c.serv_id
and b.serv_id = d.serv_id
and d.name = 'XXX'
name会不会重复
哪些字段加了索引楼上的B和C表不能直接关联,不是主键关联会产生重复数据
原贴如下,楼住的sql是我写的,不知道如何优化
我单个的SQL语句是怎么写的就是正确结果,连表以后不知怎么回事就错了
select serv_id from d where name='XXXX';
select acct_id from b where serv_id = XXXX;
select sum(balance) from a where acct_id =XXXX;
select sum(charge) from c where serv_id = 239237 ;
我想直接通过4表连接得到sum(balance)-sum(charge)值,怎么弄啊?表中的数据也太多,假如让我算1000个号码的余额,一个一个来太慢,请高手支招
后面漏了group by了吧select d.name,sum(c.change-a.blance) from a,b,c,d
where b.acct_id = a.acct_id
and b.serv_id = c.serv_id
and b.serv_id = d.serv_id
and d.name = 'XXX'
group by d.name你的表的字段少,可以考虑把它们搞成IOT
正如3楼所说,其实没有必要选出name的,这样也就不需要加group by了。