to libin_ftsafe(子陌红尘) 是的to zjcxc(邹建) 差值是按id前四位分组之后,从min到max是否连续判断。to godofwind(刘钰文) 可能我的描述不清楚吧
select min(b.id)+1 from (select id from t1 a where (a.id+1) < (select min(id) from t1 where id > a.id and (id/1000) = (a.id/1000))) b
--示例--测试数据 create table tb(id int) insert tb select 10010001 union all select 10010002 --union all select 10010003 --union all select 10010004 union all select 10010005 union all select 10020001 --union all select 10020002 --union all select 10020003 union all select 10020004 union all select 10030001 go--查id的处理 select 新id=left(id,4)+right(10001+right(a.id,4),4) from( select id=isnull(min(a.id),(select max(id) from tb)) from tb a left join( select id=max(id) from tb group by left(id,4) )b on a.id=b.id where b.id is null and not exists( select * from tb where id=left(a.id,4)+right(10001+right(a.id,4),4)) )a go--删除测试 drop table tb/*--测试结果新id ---------------- 10010003(所影响的行数为 1 行) --*/
--上面的处理,如果缺少最小号 xxxx0001,则查不出来,下面这个考虑了这个问题--示例--测试数据 create table tb(id int) insert tb select 10010001 union all select 10010002 union all select 10010003 union all select 10010004 union all select 10010005 --union all select 10020001 --union all select 10020002 --union all select 10020003 union all select 10020004 union all select 10030001 go--查id的处理 select 新id=left(id,4)+right(10001+right(a.id,4),4) from( select id=isnull(min(a.id),(select max(id) from tb)) from( select id from tb union all select distinct left(id,4)+'0000' from tb )a left join( select id=max(id) from tb group by left(id,4) )b on a.id=b.id where b.id is null and not exists( select * from tb where id=left(a.id,4)+right(10001+right(a.id,4),4)) )a go--删除测试 drop table tb/*--测试结果新id ---------------- 10020001(所影响的行数为 1 行) --*/
根据邹建的思路,纠正了一下我的代码:select min(b.id)+1 from (select id from t1 a where (a.id+1) < (select min(id) from t1 where id > a.id and (id/1000) = (a.id/1000)) union select c.step*1000 as id from (select id/1000 as step,min(id) as id from t1 group by id/1000) c where c.id%1000<>1) b
是的to zjcxc(邹建)
差值是按id前四位分组之后,从min到max是否连续判断。to godofwind(刘钰文)
可能我的描述不清楚吧
min(b.id)+1
from
(select
id
from
t1 a
where
(a.id+1) < (select min(id) from t1 where id > a.id and (id/1000) = (a.id/1000))) b
create table tb(id int)
insert tb select 10010001
union all select 10010002
--union all select 10010003
--union all select 10010004
union all select 10010005
union all select 10020001
--union all select 10020002
--union all select 10020003
union all select 10020004
union all select 10030001
go--查id的处理
select 新id=left(id,4)+right(10001+right(a.id,4),4)
from(
select id=isnull(min(a.id),(select max(id) from tb))
from tb a left join(
select id=max(id) from tb group by left(id,4)
)b on a.id=b.id
where b.id is null and not exists(
select * from tb where id=left(a.id,4)+right(10001+right(a.id,4),4))
)a
go--删除测试
drop table tb/*--测试结果新id
----------------
10010003(所影响的行数为 1 行)
--*/
create table tb(id int)
insert tb select 10010001
union all select 10010002
union all select 10010003
union all select 10010004
union all select 10010005
--union all select 10020001
--union all select 10020002
--union all select 10020003
union all select 10020004
union all select 10030001
go--查id的处理
select 新id=left(id,4)+right(10001+right(a.id,4),4)
from(
select id=isnull(min(a.id),(select max(id) from tb))
from(
select id from tb
union all
select distinct left(id,4)+'0000' from tb
)a left join(
select id=max(id) from tb group by left(id,4)
)b on a.id=b.id
where b.id is null and not exists(
select * from tb where id=left(a.id,4)+right(10001+right(a.id,4),4))
)a
go--删除测试
drop table tb/*--测试结果新id
----------------
10020001(所影响的行数为 1 行)
--*/
1.如果缺少的是最小号 xxxx0001,则查询结果是null
2.如果没有缺号,则查询结果是null
min(b.id)+1
from
(select
id
from
t1 a
where
(a.id+1) < (select min(id) from t1 where id > a.id and (id/1000) = (a.id/1000))
union
select
c.step*1000 as id
from
(select id/1000 as step,min(id) as id from t1 group by id/1000) c
where
c.id%1000<>1) b
另外,楼上我写的SQL语句要把1000改成10000,我看少了一个"0".