查询2009年没有下雨的日期

Question

表结构：
Id int 自动编号 
StartTime  datetime  开始下雨的时间
EndTime datetime 停止下雨的时间（可能是隔几天）上表记录了下雨的日期问：如何查找2009年没有下雨的日期

Answer 1

create table tb(StartTime datetime,EndTime datetime)
insert into tb values('2009-01-01' , '2009-01-10')
insert into tb values('2009-02-01' , '2009-11-20')
go--使用一个临时表
SELECT TOP 8000 id = IDENTITY(int, 0, 1) INTO # FROM syscolumns a, syscolumns b select distinct p.dt from
(
  select dateadd(day , n.id , '2009-01-01') dt from tb m , # n where dateadd(day , n.id , '2009-01-01') <= '2009-12-31' 
) p where dt not in
(select dateadd(day , n.id , m.StartTime) dt from tb m, # n where dateadd(day , n.id , m.StartTime) <= EndTime)
order by dtdrop table tb , #/*
dt                                                     
------------------------------------------------------ 
2009-01-11 00:00:00.000
2009-01-12 00:00:00.000
2009-01-13 00:00:00.000
2009-01-14 00:00:00.000
2009-01-15 00:00:00.000
2009-01-16 00:00:00.000
2009-01-17 00:00:00.000
2009-01-18 00:00:00.000
2009-01-19 00:00:00.000
2009-01-20 00:00:00.000
2009-01-21 00:00:00.000
2009-01-22 00:00:00.000
2009-01-23 00:00:00.000
2009-01-24 00:00:00.000
2009-01-25 00:00:00.000
2009-01-26 00:00:00.000
2009-01-27 00:00:00.000
2009-01-28 00:00:00.000
2009-01-29 00:00:00.000
2009-01-30 00:00:00.000
2009-01-31 00:00:00.000
2009-11-21 00:00:00.000
2009-11-22 00:00:00.000
2009-11-23 00:00:00.000
2009-11-24 00:00:00.000
2009-11-25 00:00:00.000
2009-11-26 00:00:00.000
2009-11-27 00:00:00.000
2009-11-28 00:00:00.000
2009-11-29 00:00:00.000
2009-11-30 00:00:00.000
2009-12-01 00:00:00.000
2009-12-02 00:00:00.000
2009-12-03 00:00:00.000
2009-12-04 00:00:00.000
2009-12-05 00:00:00.000
2009-12-06 00:00:00.000
2009-12-07 00:00:00.000
2009-12-08 00:00:00.000
2009-12-09 00:00:00.000
2009-12-10 00:00:00.000
2009-12-11 00:00:00.000
2009-12-12 00:00:00.000
2009-12-13 00:00:00.000
2009-12-14 00:00:00.000
2009-12-15 00:00:00.000
2009-12-16 00:00:00.000
2009-12-17 00:00:00.000
2009-12-18 00:00:00.000
2009-12-19 00:00:00.000
2009-12-20 00:00:00.000
2009-12-21 00:00:00.000
2009-12-22 00:00:00.000
2009-12-23 00:00:00.000
2009-12-24 00:00:00.000
2009-12-25 00:00:00.000
2009-12-26 00:00:00.000
2009-12-27 00:00:00.000
2009-12-28 00:00:00.000
2009-12-29 00:00:00.000
2009-12-30 00:00:00.000
2009-12-31 00:00:00.000（所影响的行数为 62 行）*/

Answer 2

DECLARE @Table TABLE(Id INT IDENTITY(1,1), StartTime DATETIME, EndTime DATETIME)INSERT INTO @Table (StartTime,EndTime)VALUES ('2009-1-1','2009-1-11')
INSERT INTO @Table (StartTime,EndTime)VALUES ('2009-3-1','2009-4-11')DECLARE @BeginDate  DATETIME
DECLARE @EndDate    DATETIMESET @BeginDate = '2009-1-1'
SET @EndDate = '2010-1-1';WITH TEMP
AS
(
    SELECT @BeginDate + number AS riqi
    FROM master.dbo.spt_values 
    WHERE type = 'P' 
    AND number < DATEDIFF(DAY,@BeginDate,@EndDate)
)
SELECT * FROM TEMP A LEFT JOIN @Table B ON A.riqi BETWEEN B.StartTime AND EndTime
WHERE B.StartTime IS NULL

Answer 3

use PracticeDB
go
if exists(select 1 from sysobjects where name='tb')
drop table tb
go
create table tb (id int,starttime datetime,endtime datetime)
insert into tb
select 1,'2009-01-01','2009-01-10' union all
select 2,'2009-02-01','2009-11-10'select dp
from (select convert(varchar(10),DATEADD(D,number,'2009-01-01'),120) dp
      from master..spt_values 
  where type='p' and DATEADD(D,number,'2009-01-01') between '2009-01-01' and '2009-12-31') a
where dp not in (select  convert(varchar(10),DATEADD(D,number,CONVERT(varchar(10),starttime,120)),120) dt
 from master..spt_values a, tb b
 where type='p'and (DATEADD(D,number,CONVERT(varchar(10),starttime,120)) between CONVERT(varchar(10),starttime,120) and CONVERT(varchar(10),endtime,120)))
order by dpdp
2009-01-11
2009-01-12
2009-01-13
2009-01-14
2009-01-15
2009-01-16
2009-01-17
2009-01-18
2009-01-19
2009-01-20
2009-01-21
2009-01-22
2009-01-23
2009-01-24
2009-01-25
2009-01-26
2009-01-27
2009-01-28
2009-01-29
2009-01-30
2009-01-31
2009-11-11
2009-11-12
2009-11-13
2009-11-14
2009-11-15
2009-11-16
2009-11-17
2009-11-18
2009-11-19
2009-11-20
2009-11-21
2009-11-22
2009-11-23
2009-11-24
2009-11-25
2009-11-26
2009-11-27
2009-11-28
2009-11-29
2009-11-30
2009-12-01
2009-12-02
2009-12-03
2009-12-04
2009-12-05
2009-12-06
2009-12-07
2009-12-08
2009-12-09
2009-12-10
2009-12-11
2009-12-12
2009-12-13
2009-12-14
2009-12-15
2009-12-16
2009-12-17
2009-12-18
2009-12-19
2009-12-20
2009-12-21
2009-12-22
2009-12-23
2009-12-24
2009-12-25
2009-12-26
2009-12-27
2009-12-28
2009-12-29
2009-12-30
2009-12-31