【IT168技术文档】

  问题描述：PricesDaily表存有每天的价格数据，基本上是每天一条（除去周末两天和法定节假日没有）。
  现在问题是其中(以ASXCode和Date来判断)某天的数据丢失，有啥方法找到丢失这天的数据（返回丢失的ASXCode和Date）。

  PricesDaily原表数据如下：

ASXCode,Date,Price
-------------------------------------
AAA 1999-01-04 00:00:00 2.5000
AAA 1999-01-05 00:00:00 2.4200
AAA 1999-01-06 00:00:00 2.4500
AAA 1999-01-07 00:00:00 2.5100
AAA 1999-01-08 00:00:00 2.5700
AAA 1999-01-11 00:00:00 2.6200
AAA 1999-01-12 00:00:00 2.7400
AAA 1999-01-13 00:00:00 2.6300
AAA 1999-01-14 00:00:00 2.6400
AAA 1999-01-15 00:00:00 2.7200
AAA 1999-01-19 00:00:00 2.6400
AAB 2004-09-24 00:00:00 .8400
AAB 2004-09-27 00:00:00 .8400
AAB 2004-09-28 00:00:00 .8300
AAB 2004-09-29 00:00:00 .8400
AAB 2004-09-30 00:00:00 .8300
AAB 2004-10-01 00:00:00 .8300
AAB 2004-10-04 00:00:00 .8000
AAB 2004-10-05 00:00:00 .8300
AAB 2004-10-07 00:00:00 .8300
AAB 2004-10-08 00:00:00 .7900

  上面原表中，有啥方法可以查出丢失了两条（周末两天和法定节假日除外）：
  ASXCode ＝ 'AAA'，Date = '1999-01-18' 和 ASXCode ＝ 'AAB'，Date = '2004-10-06'；
  只要返回丢失的ASXCode和Date。

  使用CTE解决方法：

use Test
Declare @1 Table(ASXCode char(3),Date datetime,Price money)
Insert Into @1
Select 'AAA', '1999-01-04 00:00:00', 2.5000 Union All
Select 'AAA', '1999-01-05 00:00:00', 2.4200 Union All
Select 'AAA', '1999-01-06 00:00:00', 2.4500 Union All
Select 'AAA', '1999-01-07 00:00:00', 2.5100 Union All
Select 'AAA', '1999-01-08 00:00:00', 2.5700 Union All
Select 'AAA', '1999-01-11 00:00:00', 2.6200 Union All
Select 'AAA', '1999-01-12 00:00:00', 2.7400 Union All
Select 'AAA', '1999-01-13 00:00:00', 2.6300 Union All
Select 'AAA', '1999-01-14 00:00:00', 2.6400 Union All
Select 'AAA', '1999-01-15 00:00:00', 2.7200 Union All
Select 'AAA', '1999-01-19 00:00:00', 2.6400 Union All
Select 'AAB', '2004-09-24 00:00:00', .8400 Union All
Select 'AAB', '2004-09-27 00:00:00', .8400 Union All
Select 'AAB', '2004-09-28 00:00:00', .8300 Union All
Select 'AAB', '2004-09-29 00:00:00', .8400 Union All
Select 'AAB', '2004-09-30 00:00:00', .8300 Union All
Select 'AAB', '2004-10-01 00:00:00', .8300 Union All
Select 'AAB', '2004-10-04 00:00:00', .8000 Union All
Select 'AAB', '2004-10-05 00:00:00', .8300 Union All
Select 'AAB', '2004-10-07 00:00:00', .8300 Union All
Select 'AAB', '2004-10-08 00:00:00', .7900
;With T1
As
(
Select ASXCode,Startdate=Min(Date),EndDate=Max(Date)
From @1
Group By ASXCode
)
,T2
As
(
Select ASXCode,Date=Startdate
From T1
Union All
Select a.ASXCode,Date=a.Date+1
From T2 a,T1 b Where a.ASXCode=b.ASXCode And a.Date<b.EndDate
)
Select a.*
From T2 a
Left Outer Join @1 b On b.ASXCode=a.ASXCode And a.Date=b.Date
Where b.Date Is null And Datename(dw,a.date) Not In('星期六','星期日')
Option(Maxrecursion 0)
--其他假日条件可以自由加入

/*
(21 行受影响)
ASXCode Date
------- -----------------------
AAB     2004-10-06 00:00:00.000
AAA     1999-01-18 00:00:00.000
(2 行受影响)
*/

  CTE默认可以递归100层，所以使用Option(Maxrecursion 0)参数，
  CTE目前最大可以32767层，从我们现实考虑（Select 32767/365.0=89.772602）可以查询89年的数据，已经够用了呵呵。