找出表中缺失的连续数据(如:2,4,7,9;需要找出:1,3,5,6,8的数据)

--通过找出排名最大值，构造正确数据，把表数据与正确数据进行对比，进而找出缺失的数据
--1.构造临时表
IF OBJECT_ID('tempdb..#table_test') IS NOT NULL DROP TABLE #table_test
SELECT ExchangeCode,ContractInnerCode,IndicatorCode,EndDate,MAX(RankNumber) RankNumber_max
into #table_test
FROM Fut_MemberRankByContract
WHERE EndDate between '2009-07-23 00:00:00.000' AND '2009-08-23 00:00:00.000'
GROUP BY ExchangeCode,ContractInnerCode,IndicatorCode,EndDate

--2.生成正确数据
declare @ExchangeCode int,@ContractInnerCode int,@IndicatorCode int,@EndDate datetime,@RankNumber_max int

declare Cur_pm cursor local for
select ExchangeCode,ContractInnerCode,IndicatorCode,EndDate,RankNumber_max
from #table_test
order by ExchangeCode,ContractInnerCode,IndicatorCode,EndDate --定义游标 Cur_pm

OPEN Cur_pm --打开游标
WHILE (1=1)
begin
FETCH NEXT FROM Cur_pm INTO @ExchangeCode,@ContractInnerCode,@IndicatorCode ,@EndDate,@RankNumber_max; --移动游标指向到第一条数据
if @@fetch_status !=0 break; --用于跳出循环

--执行循环
while(@RankNumber_max > 1)
begin
set @RankNumber_max = @RankNumber_max - 1
insert into #table_test
select top 1 ExchangeCode,ContractInnerCode,IndicatorCode,EndDate,@RankNumber_max
from #table_test
where ExchangeCode=@ExchangeCode and ContractInnerCode=@ContractInnerCode
and IndicatorCode=@IndicatorCode and EndDate= @EndDate
end
end
CLOSE Cur_pm --关闭游标
DEALLOCATE Cur_pm --释放游标

--3.判断缺失的数据
select a.* from
#table_test a
left join Fut_MemberRankByContract b on b.ExchangeCode=a.ExchangeCode
and b.ContractInnerCode=a.ContractInnerCode and b.IndicatorCode=a.IndicatorCode
and b.EndDate= a.EndDate and b.RankNumber = a.RankNumber_max
where b.ID is null
ORDER BY a.ExchangeCode,a.ContractInnerCode,a.IndicatorCode,a.EndDate,a.RankNumber_max