求 一 SQL 语句。。

[boaosi]

tb1:
  col1 + col2 是主键
   col1      col2     col3     
   A001       A        9        
   A001       B        4        
   A002       A        6        
   A003       B        7        
   A003       C        8

tb2:
   col1+col2 不是主键
   col1      col2     col3     
   A001       A        2        
   A001       B        1 
   A001       B        2     
   A003       B        2

我要根据col1 和col2 为条件

   select sum(col3) as col3 from tb2  where col1='A001'  and col2='B'

再用 tb1 中 该条件下col3 减去 tb2 中 sum(col3) as col3 的值，

我最后得到的值 是 ：1 （4 -3 =1）

能用一个条 sql 语句实现吗？

[快溜]

select col1,col2,sum(col3*mark) from
(select *,1 as mark from tb1
union all
select *,-1 as mark from tb2) t
where col1='A001'  and col2='B'

[guodabao]

[Quote=引用 1 楼 ssp2009 的回复:]
SQL code
select col1,col2,sum(col3*mark) from
(select *,1 as mark from tb1
union all
select *,-1 as mark from tb2) t
where col1='A001' and col2='B'
[/Quote]

这条sql语句好像不行吧...

[pengxuan]

if object_id('tb1','U') is not null
   drop table tb1
go
create table tb1
(
 col1 varchar(10),
 col2 varchar(10),
 col3 int
)
go
insert into tb1
select 'A001','A',9 union all
select 'A001','B',4 union all
select 'A002','A',6 union all
select 'A003','B',7 union all
select 'A003','C',8
go
if object_id('tb2','U') is not null
   drop table tb2
go
create table tb2
(
 col1 varchar(10),
 col2 varchar(10),
 col3 int
)
go
insert into tb2
select 'A001','A',2 union all
select 'A001','B',1 union all
select 'A001','B',2 union all
select 'A003','B',2
go
select col3-(select sum(col3) from tb2 where col1=a.col1 and col2=a.col2) from tb1 a where col1='A001' and col2='B'
go
/*
-----------
1

(1 行受影响)
*/

[wing7742]

try

SELECT a.col1,a.col2,col3=a.col3-SUM(b.col3) OVER(PARTITION BY b.col1,b.col2) FROM #tb1 a,#tb2 b
WHERE a.col1=b.col1 AND a.col2=b.col2

[--小F--]

select
 col3-(select sum(col3) from tb2 where col1=t.col1 and col2=t.col2) 
from
 tb1 t 
where
 col1='A001' and col2='B'

[dawugui]

select col1 , col2 , sum(col3) col3 from
(
select * from tb1
union all
select col1  ,    col2   ,  -col3 col3 from tb2
) t
group by col1 , col2

[dawugui]

create table tb1
(
 col1 varchar(10),
 col2 varchar(10),
 col3 int
)
insert into tb1
select 'A001','A',9 union all
select 'A001','B',4 union all
select 'A002','A',6 union all
select 'A003','B',7 union all
select 'A003','C',8
create table tb2
(
 col1 varchar(10),
 col2 varchar(10),
 col3 int
)
insert into tb2
select 'A001','A',2 union all
select 'A001','B',1 union all
select 'A001','B',2 union all
select 'A003','B',2

select col1 , col2 , sum(col3) col3 from
(
select * from tb1
union all
select col1  ,    col2   ,  -col3 col3 from tb2
) t
group by col1 , col2
order by col1 , col2

drop table tb1 , tb2

/*
col1       col2       col3        
---------- ---------- ----------- 
A001       A          7
A001       B          1
A002       A          6
A003       B          5
A003       C          8

（所影响的行数为 5 行）
*/

[苦苦的潜行者]

if object_id('tb1') is not null and object_id('tb2') is not null
drop table tb1,tb2
go
create table tb1(col1 varchar(20),col2 varchar(20),col3 int)
insert tb1
select 'A001','A',9 union all
select 'A001','B',4 union all
select 'A002','A',6 union all
select 'A003','B',7 union all
select 'A003','C',8 
go
create table tb2(col1 varchar(20),col2 varchar(20),col3 int)
insert tb2
select 'A001','A',2 union all        
select 'A001','B',1 union all 
select 'A001','B',2 union all     
select 'A003','B',2
go
select o.a-u.b as [差值] from 
(select sum(col3) as a from tb1  where col1='A001' and col2='B') o,
(select sum(col3) as b from tb2  where col1='A001' and col2='B') u  
/*
（所影响的行数为 1 行）
差值
---
1
*/
go
drop table tb1,tb2

[苦苦的潜行者]

哦,原来tb1是主表啊...可以不同统计sum(tb1.col3)了

[ILOVE_ASPNET]

create table tb1
(
 col1 varchar(10),
 col2 varchar(10),
 col3 int
)
insert into tb1
select 'A001','A',9 union all
select 'A001','B',4 union all
select 'A002','A',6 union all
select 'A003','B',7 union all
select 'A003','C',8
create table tb2
(
 col1 varchar(10),
 col2 varchar(10),
 col3 int
)
insert into tb2
select 'A001','A',2 union all
select 'A001','B',1 union all
select 'A001','B',2 union all
select 'A003','B',2


 SELECT  t.col1,t.col2,SUM(t.col3) from tb1 t where t.col1='a001' and t.col2='a' group by t.col1,t.col2
 select * from tb2 t2  where t2.col1='A001' and t2.col2='b'  

 SELECT  t.col1,t.col2,SUM(t1.col3-t.col3) FROM tb1 t1 inner  join
  (select t2.col1,t2.col2  ,sum(t2.col3) as col3  from tb2 t2 where t2.col1='A001' and t2.col2='b' group by t2.col1, t2.col2   )  as t 
 on t.col1= t1.col1 and t.col2=t1.col2   
 group by  t.col1, t.col2
 
 /*
 col1       col2       
---------- ---------- -----------
A001       B          1

(1 行受影响)
 */

[ILOVE_ASPNET]

 SELECT  t.col1,t.col2,SUM(t.col3) from tb1 t where t.col1='a001' and t.col2='a' group by t.col1,t.col2
 select * from tb2 t2  where t2.col1='A001' and t2.col2='b'  

上贴多了，这个是不要的