tb1:
col1 + col2 是主键
col1 col2 col3
A001 A 9
A001 B 4
A002 A 6
A003 B 7
A003 C 8tb2:
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 语句实现吗?
col1 + col2 是主键
col1 col2 col3
A001 A 9
A001 B 4
A002 A 6
A003 B 7
A003 C 8tb2:
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 *,1 as from tb1
union all
select *,-1 as from tb2) t
where col1='A001' and col2='B'
这条sql语句好像不行吧...
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 行受影响)
*/
WHERE a.col1=b.col1 AND a.col2=b.col2
col3-(select sum(col3) from tb2 where col1=t.col1 and col2=t.col2)
from
tb1 t
where
col1='A001' and col2='B'
(
select * from tb1
union all
select col1 , col2 , -col3 col3 from tb2
) t
group by col1 , col2
(
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',2select 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 , col2drop table tb1 , tb2/*
col1 col2 col3
---------- ---------- -----------
A001 A 7
A001 B 1
A002 A 6
A003 B 5
A003 C 8(所影响的行数为 5 行)
*/
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
(
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 行受影响)
*/
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' 上贴多了,这个是不要的