你的位置:首页 > 数据库

[数据库]找出范围内丢失的数据


数据库环境 SQL SERVER2008R2

需求:有一个表的日期字段,存的是每天的日期,且该字段存在唯一性约束。由于管理员误操作,把当前月的一些日期给删了,

现在要把被删除的日期给找出来。

实现思路:创建一个日期辅助表,辅助表存的是当前月的所有日期,再和目标表左关联,日期对应不上的则是丢失的日期。

数据准备:

1.创建一个1-100的自然数列表

SELECT TOP 100    id = IDENTITY( INT,1,1 )INTO  dbo.t100FROM  sysobjects

2.模拟生成丢失日期的目标表,日期对应的天数能被3或者7整除的都是丢失的日期

/*取到当前月的月初和月末日期*/WITH  x0     AS ( SELECT  CONVERT(DATE, DATEADD(d, -DAY(GETDATE()) + 1,                       GETDATE())) AS date_begin ,            CONVERT(DATE, DATEADD(d, -DAY(GETDATE()),                       DATEADD(m, 1, GETDATE()))) AS date_end       ),/*生成日期序号*/    x1     AS ( SELECT  date_begin ,            date_end ,            DATEDIFF(d, date_begin, date_end) + 1 AS nday        FROM   x0       ),/*遍历本月的日期*/    x2     AS ( SELECT  ddate ,            id        FROM   ( SELECT  DATEADD(d, id - 1, date_begin) AS ddate ,                  id             FROM   t100                  LEFT JOIN x1 ON t100.id <= x1.nday            ) t        WHERE  ddate IS NOT NULL       )  SELECT ddate  INTO  testDate  FROM  x2  WHERE  ( id % 3 != 0 )      AND ( id % 7 != 0 )

3.找出缺失的日期

SELECT x2.*FROM  x2    LEFT JOIN testDate ON x2.ddate = testDate.ddateWHERE  testDate.ddate IS NULL

好了,我们来对比一下数据,左边是目标表,右边是缺失日期数据集,刚好对得上。

缺失日期