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.类别
---------------------------------------------------------------- -- 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 行受影响)*/
select 类别,sum(库存) as [库存] from (select 类别,库存 from Tb1 union all select 类别,(-1)*库存 from Tb2)T group by 类别
select t.AA,t.库存-r.库存 as 库存 from AA t join (select 类别,sum(库存) as 库存 from BB group by 类别 ) r on t.类别 = r.类别
(
select 类别 , 库存 from aa
union all
select 类别 ,-库存 from bb
)t
group by 类别
-- 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 行受影响)*/
(select 类别,库存 from Tb1
union all
select 类别,(-1)*库存 from Tb2)T
group by 类别
t.AA,t.库存-r.库存 as 库存
from AA t join
(select 类别,sum(库存) as 库存 from BB group by 类别 ) r
on t.类别 = r.类别