select a.name,a.money,b.tel,b.sms,balance=a.money-b.tel-b.sms from a, (select name,tel=sum(tel),sms=sum(sms) from b group by name)b where a.name=b.name
不用游标吧select a.name,a.money,k.tel.k.sms,balance=a.money-k.money-k.tel from a,(select name,tel=sum(tel),sms=sum(sms) from b group by name) k where a.name=k.name
SELECT A.*, ISNULL(B.tel,0) AS tel, ISNULL(B.sms,0) AS sms, A.money-ISNULL(B.tel,0)-ISNULL(B.sms,0) AS balance FROM 表A AS A LEFT JOIN (SELECT name,SUM(tel) AS tel,SUM(sms) AS sms FROM 表B GROUP BY name) AS B ON A.name=B.name
select a.name,a.money,b.tel,b.sms,balance=a.money-(b.tel+b.sms) from a join (select name,tel=sum(tel),sms=sum(sms) from b group by name) b on a.name=b.name
没必要用游标吧create table C(idx int identity(1,1),name varchar(10),money varchar(10),tel varchar(10),sms varchar(10),balance varchar(10))insert into C(name,money,tel,sms,balance) select A.name,A.money,B1.tel1,B1.sms from A,(select name,sum(tel)as tel1,sum(sms)as sms tel1 from B group by B)as B1 where A.name = B1.name然后根据idx导数据
from a,(select name,tel=sum(tel),sms=sum(sms) from b group by name) k
where a.name=k.name
A.*,
ISNULL(B.tel,0) AS tel,
ISNULL(B.sms,0) AS sms,
A.money-ISNULL(B.tel,0)-ISNULL(B.sms,0) AS balance
FROM 表A AS A
LEFT JOIN (SELECT name,SUM(tel) AS tel,SUM(sms) AS sms FROM 表B GROUP BY name) AS B
ON A.name=B.name
from a
join (select name,tel=sum(tel),sms=sum(sms) from b group by name) b
on a.name=b.name
select A.name,A.money,B1.tel1,B1.sms
from A,(select name,sum(tel)as tel1,sum(sms)as sms tel1 from B group by B)as B1
where A.name = B1.name然后根据idx导数据