求一SQL语句，实现查询某时间段未出现的日期

[vbangle]

表结构及测试数据如下，注意DcDate可能重复，例如下面测试数据中2009-10-02和2009-10-07就有2个

ID DcDate
694 2009-10-01
695 2009-10-02
696 2009-10-02
697 2009-10-04
698 2009-10-05
699 2009-10-07
700 2009-10-07
701 2009-10-08
702 2009-10-09


如上，要查询2009-10-01至2009-10-09期间未出现的日期，查询结果为：

2009-10-03
2009-10-06

谢谢！

[jwdream2008]

up!

[SQL77]

SELECT TIME FROM 
(
SELECT CONVERT(VARCHAR(10),DATEADD(DD,NUMBER,'2009-10-01'),120)AS TIME 
FROM MASTER..SPT_VALUES WHERE TYPE='P' AND DATEADD(DD,NUMBER,'2009-10-01')<='2009-10-09' )AS T 
WHERE TIME NOT IN (SELECT CONVERT(VARCHAR(10),DCDATE,120) FROM TB )

[nianran520]

select dateadd(day,number,'2009-10-01') as DcDate from master..spt_values where type='P'
and dateadd(day,number,'2009-10-01')<='2009-10-09'
and dateadd(day,number,'2009-10-01') not in 
(select DcDate from table)

[7761098]

with tb(val) as
(
select '2009-10-01'
union all
select convert(varchar(10),dateadd(dd,1,convert(datetime,val)),120) from tb where val<'2009-10-09'
)
select A.DcDate from tb left join A on tb.val = A.DcDate where A.DcDate is null

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-10-27 16:06:37
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ID] int,[DcDate] datetime)
insert [tb]
select 694,'2009-10-01' union all
select 695,'2009-10-02' union all
select 696,'2009-10-02' union all
select 697,'2009-10-04' union all
select 698,'2009-10-05' union all
select 699,'2009-10-07' union all
select 700,'2009-10-07' union all
select 701,'2009-10-08' union all
select 702,'2009-10-09'
--------------开始查询--------------------------

DECLARE @mindate datetime,@maxdate datetime;
SELECT @mindate = '2009-10-01',@maxdate = '2009-10-09';

SELECT 
   DATEADD(day,number,@mindate) AS date
FROM 
   master.dbo.spt_values
WHERE 
  type = 'p' 
AND 
  DATEADD(day,number,@mindate) <= @maxdate 
and 
  DATEADD(day,number,@mindate) not in (select DcDate from tb) ;

----------------结果----------------------------
/* date
-----------------------
2009-10-03 00:00:00.000
2009-10-06 00:00:00.000

(2 行受影响)
*/

[navy887]

select dateadd(day,number,'2009-10-01')  
from master..spt_values 
where type='P' and number between 1 and 9
and number not in (select day(dcdate) from tb)

[feixianxxx]

    create table #oo (id int,dcdate datetime)
insert #oo select 
694 ,'2009-10-01' union all select 
695 ,'2009-10-02' union all select 
696 ,'2009-10-02' union all select 
697 ,'2009-10-04' union all select 
698 ,'2009-10-05' union all select 
699 ,'2009-10-07' union all select 
700 ,'2009-10-07' union all select 
701 ,'2009-10-08' union all select 
702 ,'2009-10-09' 
select convert(varchar(10),k.rq,120) as dcdate
from (select DATEADD(DAY,number,(select MIN(dcdate) from #oo))  as rq
from master..spt_values where type='p' and
number<=(select DATEDIFF(DAY,(select MIN(dcdate) from #oo),(select Max(dcdate) from #oo)))) k
left join #oo o on k.rq=o.dcdate
where o.dcdate is null 
/*
dcdate
----------
2009-10-03
2009-10-06
*/

[jwdream2008]

学习！

[lass_name]

up

[vbangle]

要是统计1）期间大于2009-10-04未出现的日期
　　　　2）期间小于2009-10-05未出现的日期

各怎样写？

[vbangle]

另外，5＃和7＃由于是通过master..spt_values 的方法，经测试好像最多只能返回256条记录，如果1楼的查询期间改为2009-07-01至2009-10-09期间未出现的日期，那么就只能返回前256条记录了，有什么其它的方法实现超过256条返回的情形？

[SQL77]

[Quote=引用 11 楼 vbangle 的回复:]
另外，5＃和7＃由于是通过master..spt_values 的方法，经测试好像最多只能返回256条记录，如果1楼的查询期间改为2009-07-01至2009-10-09期间未出现的日期，那么就只能返回前256条记录了，有什么其它的方法实现超过256条返回的情形？
[/Quote]
当然可以,你可以利用ID自增列实现,只要将NUMBER改为ID