create table tb1(id varchar(10),data int)select a.id,a.data+b.data
from (select * from tb1 where data>4) a ,(select * from tb2 where data>4)
where a.id=b.id
from (select * from tb1 where data>4) a ,(select * from tb2 where data>4)
where a.id=b.id
create table tb1(id varchar(10),data int) create table tb2(id varchar(10),data int) insert into tb1
select 's100',6 union all
select 's200',1 union all
select 's300',5 union all
select 's400',2
insert into tb2
select 's100',1 union all
select 's200',3 union all
select 's300',4 union all
select 's900',6 select
id,
sum(data) total_data
from
(
select * from tb1
union all
select * from tb2
)tb3
group by id
having sum(data) > 4
drop table tb1
drop table tb2结果:
s100 7
s300 9
s900 6
Set Nocount On
if not object_id('tb1') is null
drop table tb1
Go
Create table tb1([a] nvarchar(4),[b] int)
Insert tb1
select N's100',6 union all
select N's200',1 union all
select N's300',5 union all
select N's400',2
Go
if not object_id('tb2') is null
drop table tb2
Go
Create table tb2([a] nvarchar(4),[b] int)
Insert tb2
select N's100',1 union all
select N's200',3 union all
select N's300',4 union all
select N's900',6
Go
Select a.a, a.b+Isnull(b.b,0) As b
From tb1 a
Left Outer Join tb2 b On b.a=a.a
Where a.b+Isnull(b.b,0)>4
Union
Select a.a, a.b+Isnull(b.b,0) As b
From tb2 a
Left Outer Join tb1 b On b.a=a.a
Where a.b+Isnull(b.b,0)>4
/*
a b
---- -----------
s100 7
s300 9
s900 6
*/
select col1,sum(col2) from (select * from tb1 union all select * from tb2) group by col1 having sum(col2)>4
select col1,sum(col2) from (select * from tb1 union all select * from tb2) tb group by col1 having sum(col2)>4
(tb1.couml2+tb2.couml2)>4