use pubs
go
create table rk
(
id int,
name varchar(50),
sl int
)
insert into rk values(1,'a',10)
insert into rk values(2,'a',3)
insert into rk values(3,'b',5)
insert into rk values(4,'b',6)
create table ck
(
id int,
name varchar(50),
sl int
)
insert into ck values(1,'a',2)
insert into ck values(2,'a',4)
insert into ck values(3,'b',1)
insert into ck values(4,'b',3)select name,sum(sl) from rk group by name
select name,sum(sl) from ck group by name/*
怎么才能得到这样的结果
name 结余数
a 7
b 7
*/drop table rk
drop table ck
select name,sum(sl) as 结余数
from
(
select name,sl from rk
union all
select name,-sl from ck
) t
group by name/**
name 结余数
-------------------------------------------------- -----------
a 7
b 7(所影响的行数为 2 行)
**/
select Name, sum(case when flag=1 then sl else -sl end) as s1
from
(
select Name, sl,flag=1
from rk
union all
select Name, sl,flag=2
from ck
) A
group by Name