27,580
社区成员
发帖
与我相关
我的任务
分享
create table A(id int,name varchar(10),number int)
insert into a values(1 , 'a' , 12 )
insert into a values(2 , 'b' , 10 )
create table B(id int,name varchar(10),num int)
insert into b values(1 , 'a' , 2 )
insert into b values(2 , 'a' , 3 )
insert into b values(3 , 'a' , 4 )
go
select a.id ,
a.name,
result = number - isnull((select sum(num) from b where name = a.name),0)
from a
drop table a , b
/*
id name result
----------- ---------- -----------
1 a 3
2 b 10
(所影响的行数为 2 行)
*/
select t.id ,
t.name,
number - isnull((select sum(num) from b where name = t.name),0)
from a t
select r.name,sum(r.number)-isnull(sum(t.num),0)
from A r left join B t
on r.name=t.name
group by r.name