两表相减

[luyesql]

AA

类别 库存
A 997
B 1234


BB

类别 库存
A 105
A 116
B 214

用sql 得出A,B各剩多少

[水族杰纶]

select 类别,sum(库存)库存 from 
(
select 类别 , 库存 from aa
union all
select 类别 ,-库存 from bb
)t
group by 类别

[黄_瓜]

select aa.类别， sum(aa.库存-sum(b.库存)) from aa, bb group by aa.类别

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2010-01-26 10:39:07
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[AA]
if object_id('[AA]') is not null drop table [AA]
go 
create table [AA]([类别] varchar(1),[库存] int)
insert [AA]
select 'A',997 union all
select 'B',1234
--> 测试数据：[BB]
if object_id('[BB]') is not null drop table [BB]
go 
create table [BB]([类别] varchar(1),[库存] int)
insert [BB]
select 'A',105 union all
select 'A',116 union all
select 'B',214
--------------开始查询--------------------------
select
 类别,sum(库存) as num
from
(
 select 类别,库存 from aa
 union all
 select 类别,-库存 from bb
)t
group by
  类别
----------------结果----------------------------
/*类别   num
---- -----------
A    776
B    1020

(2 行受影响)

*/

[Mr_Nice]

select 类别,sum(库存) as [库存] from 
(select 类别,库存 from Tb1
union all
select 类别,(-1)*库存 from Tb2)T
group by 类别

[nianran520]

select
t.AA,t.库存-r.库存 as 库存
from AA t join 
(select 类别,sum(库存) as 库存 from BB group by 类别 ) r
on t.类别 = r.类别

[黄_瓜]

[Quote=引用 1 楼 wufeng4552 的回复:]
SQL codeselect 类别,sum(库存)库存from
(select 类别 , 库存from aaunionallselect 类别 ,-库存from bb
)tgroupby 类别
[/Quote]
bu cuo

[ACMAIN_CHM]

[Quote= #1　wufeng4552(]select 类别,sum(库存)库存 from
(
select 类别 , 库存 from aa
union all
select 类别 ,-库存 from bb
)t
group by 类别[/Quote]

这个不错

[SQL77]

[Quote=引用 1 楼 wufeng4552 的回复:]
SQL codeselect 类别,sum(库存)库存from
(select 类别 , 库存from aaunionallselect 类别 ,-库存from bb
)tgroupby 类别
[/Quote]
标准写法