--如何用"最小缺失数"实现日期的自动补全
-->生成测试数据:
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
日期 DATE,
备注 VARCHAR(100)
)
GO
INSERT TBL
SELECT '2012-03-02','B' UNION ALL
SELECT '2012-03-05','C' UNION ALL
SELECT '2012-03-06','D' UNION ALL
SELECT '2012-03-07','E' UNION ALL
SELECT '2012-03-09','F' UNION ALL
SELECT '2012-03-11','G' UNION ALL
SELECT '2012-03-12','H' UNION ALL
SELECT '2012-03-13','I' UNION ALL
SELECT '2012-03-15','J' UNION ALL
SELECT '2012-03-19','K' UNION ALL
SELECT '2012-03-20','L'


GO
IF OBJECT_ID('P_SP')IS NOT NULL
DROP PROC P_SP
GO
CREATE PROC P_SP @ENDTIME DATE
AS
DECLARE @SQL VARCHAR(100)
SET @SQL='SELECT * FROM TBL ORDER BY 日期'
DECLARE @MINMISS DATE
SET @MINMISS=(
SELECT COALESCE(DATEADD(DD,1,MIN(A.日期)),
'2012-03-01') AS MISSING
FROM TBL A
WHERE NOT EXISTS(
SELECT * FROM TBL B 
WHERE B.日期=DATEADD(DD,1,A.日期))
AND EXISTS (
SELECT 1 FROM TBL WHERE 日期='2012-03-01'))
PRINT @MINMISS
WHILE @MINMISS<=@ENDTIME
BEGIN
INSERT TBL(日期) VALUES(@MINMISS)
SELECT @MINMISS=(
SELECT DATEADD(DD,1,MIN(A.日期))
FROM TBL A
WHERE NOT EXISTS(
SELECT * FROM TBL B 
WHERE B.日期=DATEADD(DD,1,A.日期))
)
END
EXEC(@SQL)

EXEC P_SP '2012-03-20'

/*
日期    备注
2012-03-01    NULL
2012-03-02    B
2012-03-03    NULL
2012-03-04    NULL
2012-03-05    C
2012-03-06    D
2012-03-07    E
2012-03-08    NULL
2012-03-09    F
2012-03-10    NULL
2012-03-11    G
2012-03-12    H
2012-03-13    I
2012-03-14    NULL
2012-03-15    J
2012-03-16    NULL
2012-03-17    NULL
2012-03-18    NULL
2012-03-19    K
2012-03-20    L
*/

--------------------------------------------------
--------------------------------------------------
-->生成测试数据:
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
日期 DATE
)
GO
INSERT TBL
SELECT '2012-03-01' UNION ALL
SELECT '2012-03-31'


--利用递归实现输出三月份的所有日期：
go
declare @date date
select @date=MAX(日期) from tbl
;with t
as(
select * from tbl
union all
select dateadd(dd,1,a.日期) from t a
where not exists(select * from tbl b
where b.日期=DATEADD(DD,1,a.日期)
)
and a.日期<@date
)
select *from t order by 日期

/*
日期
2012-03-01
2012-03-02
2012-03-03
2012-03-04
2012-03-05
2012-03-06
2012-03-07
2012-03-08
2012-03-09
2012-03-10
2012-03-11
2012-03-12
2012-03-13
2012-03-14
2012-03-15
2012-03-16
2012-03-17
2012-03-18
2012-03-19
2012-03-20
2012-03-21
2012-03-22
2012-03-23
2012-03-24
2012-03-25
2012-03-26
2012-03-27
2012-03-28
2012-03-29
2012-03-30
2012-03-31
*/