如何实现补全表中不存在的日期
比如说3月份，我表里已经有1，2，5，6,7，12,15,16....
但是时间不连续，我怎让他连续
------解决方案--------------------
- SQL code
--如何用"最小缺失数"实现日期的自动补全-->生成测试数据: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*/