如表:
字段 ID
10001 1
10002 2
10002 3
10002 4
10003 5
10003 6
10004 7 ---出错内容
10005 8
10004 9
10005 10 ---字段值应该是递增的,而出错的地方打乱了这个排列,导致数据错误,希望能找出来
10006 11
10007 12 ---出错内容
10008 13
10007 14
10008 15 ---
10009 16
........
要求查询出如上出错的地方,只要有个提示就OK了,如10004,10005,10007,10008
字段 ID
10001 1
10002 2
10002 3
10002 4
10003 5
10003 6
10004 7 ---出错内容
10005 8
10004 9
10005 10 ---字段值应该是递增的,而出错的地方打乱了这个排列,导致数据错误,希望能找出来
10006 11
10007 12 ---出错内容
10008 13
10007 14
10008 15 ---
10009 16
........
要求查询出如上出错的地方,只要有个提示就OK了,如10004,10005,10007,10008
insert into # values(10002)
insert into # values(10002)
insert into # values(10002)
insert into # values(10003)
insert into # values(10003)
insert into # values(10004)
insert into # values(10005)
insert into # values(10004)
insert into # values(10005)
insert into # values(10006)
insert into # values(10007)
insert into # values(10008)
insert into # values(10007)
insert into # values(10008)
insert into # values(10009)
select col,id from(
select top 100 PERCENT iid=(select count(1) from # where col<=a.col and id<=a.id),* from # a order by col,id
) b where iid<>iddrop table #col id
----------- -----------
10004 9
10007 14(2 行受影响)
select @id = 0,@s = ''
update # set @s = @s + case when @id > col then rtrim(col)+' '+rtrim(id)+',' else '' end,@id = col
select @s