用SQL查询数据库中不存在的数据？解决办法

create table #date(name varchar(50),[date] datetime)
insert into #date
select 'aa','2009-01-01' union select
'aa','2009-02-02' union select
'aa', '2009-03-03' union select
'aa','2009-07-04' union select
'aa','2009-06-08'

select * from #date

declare @startdate varchar(30)
declare @enddate varchar(30)
set @startdate = '2009-09'
set @enddate = '2009-11'


--求出所有的时间段，这个稍微复杂点
select convert(varchar(4),a.number)+'-'+right('0'+convert(varchar(2),b.number),2) as year_mon into #temp 
from master..spt_values a
,master..spt_values b 
where a.type='p' and b.type='p'
and b.number<=12 and b.number>0
and a.number>='1900'  and a.number<'2048'
and cast(convert(varchar(4),a.number)+'/'+convert(varchar(2),b.number)+'/01' as datetime)>=@startdate+'-01'
and cast(convert(varchar(4),a.number)+'/'+convert(varchar(2),b.number)+'/01' as datetime)<=@enddate+'-01'


select 'aa' name,* from #temp 
where year_mon not in (select CONVERT(varchar(7),[date],120) from #date where ISNULL([date],'')<>'')

------解决方案--------------------

/*
name time
aa 2009-01-01
aa 2009-02-02
aa 2009-03-03
aa 2009-07-04
aa 2009-06-08

查询出
name time
aa 2009-04-01
aa 2009-05-02
*/
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
[name] varchar(2),
[time] date
)
go
insert tbl
select 'aa','2009-01-01' union all
select 'aa','2009-02-02' union all
select 'aa','2009-03-03' union all
select 'aa','2009-07-04' union all
select 'aa','2009-06-08'


select c.[time],isnull(c.name,'aa') as name from  
(select  
isnull([time],'2010-'+right('00'+ltrim(number),2)) as [time],--实现按月份递增
  a.name
  from master..spt_values  b
  left join
  (
  select convert(varchar(7),[time],120) as [time] ,name from tbl
  ) a
  on b.number=month(a.[time]+'-01')
  where b.type='p' and b.number between 1 and 7
  )c  
  where c.[time] not in (select convert(varchar(7),[time],120) as [time] from tbl)
  
  /*
  time	name
2010-04	aa
2010-05	aa
  */


不知道你月的后面的日期想怎么处理

这个能看懂吧