table1
PlanID InAmount InDate
081210001 50 2008-12-9
081210002 80 2008-12-8
081211001 20 2008-12-10
081211002 30 2008-12-11table2
PlanID OutAmount OutDate
081210001 10 2008-12-10
081210002 40 2008-12-10
081210001 30 2008-12-11
081211001 10 2008-12-11
081210001 10 2008-12-11 result TB1
PlanID InAmount InDate
081210002 80 2008-12-8
081211001 20 2008-12-10
081211002 30 2008-12-11
说明:table2里的数量按PlanID汇总,和table1比较,输出毛table1 inAmount>table2 sum(OutAmount)的内容,table1里的数量不需要汇总,
PlanID InAmount InDate
081210001 50 2008-12-9
081210002 80 2008-12-8
081211001 20 2008-12-10
081211002 30 2008-12-11table2
PlanID OutAmount OutDate
081210001 10 2008-12-10
081210002 40 2008-12-10
081210001 30 2008-12-11
081211001 10 2008-12-11
081210001 10 2008-12-11 result TB1
PlanID InAmount InDate
081210002 80 2008-12-8
081211001 20 2008-12-10
081211002 30 2008-12-11
说明:table2里的数量按PlanID汇总,和table1比较,输出毛table1 inAmount>table2 sum(OutAmount)的内容,table1里的数量不需要汇总,
insert into table1 values('081210001' , 50 , '2008-12-9')
insert into table1 values('081210002' , 80 , '2008-12-8')
insert into table1 values('081211001' , 20 , '2008-12-10')
insert into table1 values('081211002' , 30 , '2008-12-11')
create table table2(PlanID varchar(20), OutAmount int, OutDate datetime)
insert into table2 values('081210001' , 10 , '2008-12-10')
insert into table2 values('081210002' , 40 , '2008-12-10')
insert into table2 values('081210001' , 30 , '2008-12-11')
insert into table2 values('081211001' , 10 , '2008-12-11')
insert into table2 values('081210001' , 10 , '2008-12-11') select PlanID , InAmount , InDate from
(
select m.* , m.InAmount - isnull((select sum(OutAmount) from table2 n where n.PlanID = m.PlanID),0) val from table1 m
) t
where val > 0drop table table1 , table2
/*
PlanID InAmount InDate
-------------------- ----------- ------------------------------------------------------
081210002 80 2008-12-08 00:00:00.000
081211001 20 2008-12-10 00:00:00.000
081211002 30 2008-12-11 00:00:00.000(所影响的行数为 3 行)*/
if object_id('table1')is not null drop table table1
go
create table table1(PlanID varchar(10), InAmount int, InDate datetime)
insert table1 select '081210001' , 50 , '2008-12-9'
insert table1 select '081210002' , 80 , '2008-12-8'
insert table1 select '081211001', 20 , '2008-12-10'
insert table1 select '081211002', 30 , '2008-12-11'
if object_id('table2')is not null drop table table2
go
create table table2(PlanID varchar(10), OutAmount int, OutDate datetime)
insert table2 select '081210001' , 10, '2008-12-10'
insert table2 select '081210002' , 40 , '2008-12-10'
insert table2 select '081210001' , 30 , '2008-12-11'
insert table2 select '081211001' , 10 , '2008-12-11'
insert table2 select '081210001', 10 , '2008-12-11'
select a.* from table1 a inner join(select PlanID,sum(OutAmount)OutAmount from table2 group by planid)b on a. InAmount>=b.OutAmount and a.planid=b.planid
/*PlanID InAmount InDate
---------- ----------- ------------------------------------------------------
081210001 50 2008-12-09 00:00:00.000
081210002 80 2008-12-08 00:00:00.000
081211001 20 2008-12-10 00:00:00.000*/