declare @表1 table (id int,name varchar(4))
insert into @表1
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五'
declare @表2 table (nameid int,奖金 int,发放奖金日期 datetime)
insert into @表2
select 1,500,'20120101' union all
select 2,600,'20120102' union all
select 2,700,'20120102' union all
select 3,800,'20120103' union all
select 3,1000,'20120104' union all
select 3,900,'20120104'
select name,sum(isnull(奖金,0)) as 奖金 from
(
select * from @表1 a full join @表2 b on a.id=b.nameid
and 发放奖金日期 between '20120102' and '20120103'
where a.id is not null
) aa group by name
/*
name 奖金
---- -----------
李四 1300
王五 800
张三 0
*/
