SQL Server2008中查询一段日期内的不存在的一个时间段解决思路

在实际生活中我们会遇到很多日期缺失的问题，但在统计的时候要求显示出缺失的日期，面对这样的问题，我们如何实现？这里我把自己学习的三种方法给大家分享一下：方法一：创建存储过程实现--如何用"最小缺失数"实现确实日期的自动补全-->生成测试数据:GOIF OBJECT_ID('TBL')IS NOT NULLDROP TABLE TBLGOCREATE TABLE TBL(日期 DATE,备注 VARCHAR(100))GOINSERT TBLSELECT '2012-03-02','B' UNION ALLSELECT '2012-03-05','C' UNION ALLSELECT '2012-03-06','D' UNION ALLSELECT '2012-03-07','E' UNION ALLSELECT '2012-03-09','F' UNION ALLSELECT '2012-03-11','G' UNION ALLSELECT '2012-03-12','H' UNION ALLSELECT '2012-03-13','I' UNION ALLSELECT '2012-03-15','J' UNION ALLSELECT '2012-03-19','K' UNION ALLSELECT '2012-03-20','L'GOIF OBJECT_ID('P_SP')IS NOT NULLDROP PROC P_SPGOCREATE PROC P_SP @ENDTIME DATEASDECLARE @SQL VARCHAR(100)SET @SQL='SELECT * FROM TBL ORDER BY 日期'DECLARE @MINMISS DATESET @MINMISS=(SELECT COALESCE(DATEADD(DD,1,MIN(A.日期)),'2012-03-01') AS MISSINGFROM TBL AWHERE NOT EXISTS(SELECT * FROM TBL B WHERE B.日期=DATEADD(DD,1,A.日期))AND EXISTS (SELECT 1 FROM TBL WHERE 日期='2012-03-01'))PRINT @MINMISSWHILE @MINMISS<[email protected]BEGININSERT TBL(日期) VALUES(@MINMISS)SELECT @MINMISS=(SELECT DATEADD(DD,1,MIN(A.日期))FROM TBL AWHERE NOT EXISTS(SELECT * FROM TBL B WHERE B.日期=DATEADD(DD,1,A.日期)))ENDEXEC(@SQL)EXEC P_SP '2012-03-20'/*日期    备注2012-03-01    NULL2012-03-02    B2012-03-03    NULL2012-03-04    NULL2012-03-05    C2012-03-06    D2012-03-07    E2012-03-08    NULL2012-03-09    F2012-03-10    NULL2012-03-11    G2012-03-12    H2012-03-13    I2012-03-14    NULL2012-03-15    J2012-03-16    NULL2012-03-17    NULL2012-03-18    NULL2012-03-19    K2012-03-20    L*/------------------------------------------------------------------------------------------------------方法二，利用递归实现：-->生成测试数据:GOIF OBJECT_ID('TBL')IS NOT NULLDROP TABLE TBLGOCREATE TABLE TBL(日期 DATE)GOINSERT TBLSELECT '2012-03-01' UNION ALLSELECT '2012-03-31'--利用递归实现输出三月份的所有日期：godeclare @date dateselect @date=MAX(日期) from tbl;with tas(select * from tblunion allselect dateadd(dd,1,a.日期) from t awhere not exists(select * from tbl bwhere b.日期=DATEADD(DD,1,a.日期))and a.日期<@date)select *from t order by 日期/*日期2012-03-012012-03-022012-03-032012-03-042012-03-052012-03-062012-03-072012-03-082012-03-092012-03-102012-03-112012-03-122012-03-132012-03-142012-03-152012-03-162012-03-172012-03-182012-03-192012-03-202012-03-212012-03-222012-03-232012-03-242012-03-252012-03-262012-03-272012-03-282012-03-292012-03-302012-03-31*/------------------------------------------------------------------------------------------------------方法三：利用系统表构造实现/*    create table #tB(    [A] int,    [C2] varchar(10),    [C3] datetime    )     insert #tB     select 1,'dfgsdfgsdf','2010-02-01' union all     select 2,'dfgsdfgsdf','2010-02-02' union all     select 3,'dfgsdfgsdf','2010-02-03' union all     select 4,'dfgsdfgsdf','2010-02-04' union all     select 4,'dfgsdfgsdf','2010-09-04' union all     select 5,'dfgsdfgsdf','2010-09-08' union all     select 5,'dfgsdfgsdf','2010-03-08' union all     select 6,'dfgsdfgsdf','2010-03-11' union all     select 4,'dfgsdfgsdf','2010-05-04' union all     select 5,'dfgsdfgsdf','2010-02-08' union all     select 6,'dfgsdfgsdf','2010-05-11' union all     select 7,'dfgsdfgsdf','2010-05-14' union all     select 8,'dfgsdfgsdf','2010-05-16' union all     select 7,'dfgsdfgsdf','2010-03-14' union all     select 8,'dfgsdfgsdf','2010-03-16' union all     select 6,'dfgsdfgsdf','2010-09-11' union all     select 7,'dfgsdfgsdf','2010-09-14' union all     select 8,'dfgsdfgsdf','2010-09-16' union all     select 9,'dfgsdfgsdf','2010-11-17'想得到如下结果SQL code    month total percent     2010-01 0 ....     2010-02 14 ....     2010-03 26 ....     2010-04 0 ....    2010-05 25 ....     2010-06 0 ....     2010-07 0 ....     2010-08 0 ....     2010-09 25 ....     2010-10 0 ....     2010-11 9 ....     2010-12 0 ....*/goif OBJECT_ID('tbl')is not nulldrop table tblgocreate table tbl([A] int,[C2] varchar(10),[C3] datetime) insert tbl select 1,'dfgsdfgsdf','2010-02-01' union all select 2,'dfgsdfgsdf','2010-02-02' union all select 3,'dfgsdfgsdf','2010-02-03' union all select 4,'dfgsdfgsdf','2010-02-04' union all select 4,'dfgsdfgsdf','2010-09-04' union all select 5,'dfgsdfgsdf','2010-09-08' union all select 5,'dfgsdfgsdf','2010-03-08' union all select 6,'dfgsdfgsdf','2010-03-11' union all select 4,'dfgsdfgsdf','2010-05-04' union all select 5,'dfgsdfgsdf','2010-02-08' union all select 6,'dfgsdfgsdf','2010-05-11' union all select 7,'dfgsdfgsdf','2010-05-14' union all select 8,'dfgsdfgsdf','2010-05-16' union all select 7,'dfgsdfgsdf','2010-03-14' union all select 8,'dfgsdfgsdf','2010-03-16' union all select 6,'dfgsdfgsdf','2010-09-11' union all select 7,'dfgsdfgsdf','2010-09-14' union all select 8,'dfgsdfgsdf','2010-09-16' union all select 9,'dfgsdfgsdf','2010-11-17'select isnull(c1,'2010-'+right('00'+ltrim(number),2)) as [month],--实现按月份递增isnull(c2,0) as total,ltrim(cast(isnull(c2,0)*100*1.0/(select sum([A]) from tbl) as decimal(18,2)))+'%' as [percent]--求百分比 from master..spt_values  b left join (select convert(varchar(7),C3,120) as c1,sum([A]) as c2 from tbl group by convert(varchar(7),C3,120)) c on b.number=month(c.c1+'-01') where b.type='p' and b.number between 1 and 12/*month    total    percent2010-01    0    0.00%2010-02    15    14.29%2010-03    26    24.76%2010-04    0    0.00%2010-05    25    23.81%2010-06    0    0.00%2010-07    0    0.00%2010-08    0    0.00%2010-09    30    28.57%2010-10    0    0.00%2010-11    9    8.57%2010-12    0    0.00%*/参考资料