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

最新推荐文章于 2021-03-20 16:37:08 发布

转载最新推荐文章于 2021-03-20 16:37:08 发布 · 655 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/xiaozhi1236/p/5618090.html

本文介绍了一种使用SQL脚本的方法来检查数据库中的数据完整性。通过创建临时表存储最大排名值，然后生成完整的数据集并与实际数据库中的记录进行比较，以此找出缺失的数据。此过程包括构建临时表、生成完整数据及对比缺失数据等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--通过找出排名最大值，构造正确数据，把表数据与正确数据进行对比，进而找出缺失的数据
--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

转载于:https://www.cnblogs.com/xiaozhi1236/p/5618090.html