Select gname, Sum(amount) as amount From (
Select gname, amount from t1
union
Select gname, 0 - amount from t2
) as F Group By gname==============================================================
(国)灌字准号:2002-008--001
╔╦╗╔╦╗╔╦╗╔╦╗
╠灌╣╠水╣╠许╣╠可╣
╚╩╝╚╩╝╚╩╝╚╩╝国务院灌水办(签发) CoolSlob专用
==============================================================
强烈支持EA当选 水园版主!!!!!!
Select gname, amount from t1
union
Select gname, 0 - amount from t2
) as F Group By gname==============================================================
(国)灌字准号:2002-008--001
╔╦╗╔╦╗╔╦╗╔╦╗
╠灌╣╠水╣╠许╣╠可╣
╚╩╝╚╩╝╚╩╝╚╩╝国务院灌水办(签发) CoolSlob专用
==============================================================
强烈支持EA当选 水园版主!!!!!!
from (select gname,sum(amount) as amount from t1 group by gname) as a full join (select gname,sum(amount) as amount from t2 group by gname) as b
on a.gname=b.gname
(select gname,sum(amount) as amount from a group by gname) a left outer join
(select gname,sum(amount) as amount from b group by gname) b
on a.gname=b.gname
Select gname, amount from t1
union all
Select gname, 0 - amount from t2
) as F Group By gname
from (select gname,inamount=sum(amount) from 进货表 group by gname) as a
left join
(select gname,outamount=sum(amount) from 出货表 group by gname) as b
on a.gname = b.gname
==============================================================
(国)灌字准号:2002-008--001
╔╦╗╔╦╗╔╦╗╔╦╗
╠灌╣╠水╣╠许╣╠可╣
╚╩╝╚╩╝╚╩╝╚╩╝国务院灌水办(签发) CoolSlob专用
==============================================================
强烈支持EA当选 水园版主!!!!!!
在这里union 与 union all也是有区别的!
因为t1表有两行
硬盘 5看一下测试:
select 'a' as a,1 as b
into #temp
union all
select 'b',2
union all
select 'c',3
union all
select 'a',1select * from #temp
union
select 'd',4猜一下结果是什么??
a b
---- -----------
a 1
b 2
c 3
d 4(所影响的行数为 4 行)还是:
a b
---- -----------
a 1
b 2
c 3
a 1
d 4(所影响的行数为 5 行)呵呵,是第一个。
union 与 union all的区别是:在有重复记录时是否全部选出而在这里两个表是不可能会出现重复的!