一条关于算法的问题,急,5位数字,全部由0-9组成.已知记录中已存在下列记录
00001
00003
00120
09211
12542
...求值最小的,且不存在于记录中的.一个5位数,
如上例中的解应为 00002实际的应用为生成条码的过程,主要是想利用条码中浪费的资源,把漏的补上来.效率是一个问题,
不求具体的语句,给出个思路就可以了,谢谢各位大哥大姐
00001
00003
00120
09211
12542
...求值最小的,且不存在于记录中的.一个5位数,
如上例中的解应为 00002实际的应用为生成条码的过程,主要是想利用条码中浪费的资源,把漏的补上来.效率是一个问题,
不求具体的语句,给出个思路就可以了,谢谢各位大哥大姐
create table table_name (c1 varchar(5))
insert into table_name (c1)
select '00001'
union
select '00002'
union
select '00003'
union
select '00008'
union
select '00011'
union
select '00432'
union
select '53211'--算法
select [id]=identity(int,1,1),* into #temp from table_name
select stuff('00000',6-len(min([id])),len(min([id])),min([id])) as c1 from #temp where [id]<>convert(int,c1) --刪除測試
drop table #temp
drop table table_name/*the result:*/
c1
----
00004
INSERT @tb
SELECT 00001
UNION SELECT 00003
UNION SELECT 00120
UNION SELECT 09211
UNION SELECT 12542SELECT TOP 1 REPLICATE('0',5-LEN(x.id+1)) + RTRIM(x.id+1) FROM
(SELECT a.id,nid=(SELECT COUNT(1) FROM @tb b WHERE b.id<=a.id) FROM @tb a) y
LEFT JOIN
(SELECT a.id,nid=(SELECT COUNT(1) FROM @tb b WHERE b.id<=a.id) FROM @tb a ) x
ON y.nid=x.nid+1
WHERE y.id-x.id>1 ORDER BY x.id我这里当编号为INT的,楼主要用的话,自己加个CAST转为INT就是了.不过效率说不好。
只是我的想法不知道能否实现
INSERT @tb
SELECT 00001
UNION SELECT 00003
UNION SELECT 00120
UNION SELECT 09211
UNION SELECT 12542SELECT TOP 1 REPLICATE('0',5-LEN(ISNULL(x.id,0))+1) + RTRIM(ISNULL(x.id,0)+1) FROM
(SELECT a.id,nid=(SELECT COUNT(1) FROM @tb b WHERE b.id<=a.id) FROM @tb a) y
LEFT JOIN
(SELECT a.id,nid=(SELECT COUNT(1) FROM @tb b WHERE b.id<=a.id) FROM @tb a ) x
ON y.nid=x.nid+1
WHERE y.id-x.id>1 OR ISNULL(x.id,0)=0 AND y.id<>1 ORDER BY x.id
--測試
create table table_name (c1 varchar(5))
insert into table_name (c1)
select '00001'
union all
select '00002'
union all
select '00003'
union all
select '00008'
union all
select '00011'
union all
select '00432'
union all
select '53211'
union all
select '00004'--算法
select [id]=identity(int,1,1),* into #temp from table_name order by c1
select stuff('00000',6-len(min([id])),len(min([id])),min([id])) as c1 from #temp where [id]<>convert(int,c1) --刪除測試
drop table #temp
drop table table_name
GO
--測試
Declare @Tb Table(cNo varchar(10))
insert into @Tb select '00001'
union all select '00003'
union all select '00120'
union all select '00211'
union all select '09211'
union all select '12542'select id=identity(int,1,1),cno into #temp from @Tb
select substring(cast(100000+min(id) as varchar(10)),2,5) from #Temp where cast(cno as int)<>idGO
DROP TABLE #Temp
select id=identity(int,1,1),cno into #temp from @Tb
select substring(cast(100000+min(id) as varchar(10)),2,5) from #Temp where cast(cno as int)<>id1>为表生成ID序号
2>查找ID与cNO不等的行的最小ID,即是所间隔的,然后根据你需要的位数,将其转换为字符串。
上述中是五位,所以用100000来运算。
from syscolumns
full join syscolumns a on 1=1
full join syscolumns b on 1=1select cast(substring(barcode,8,5) as bigint) as seq into #2 from productselect #1.seq from #1
left join #2
on #1.seq=#2.seq
where #2.seq is nulldrop table #1
drop table #2就是建个表放1-99999的数,然后left join, 用了3秒,不过就是不知道效率好不好.
select [id]=identity(int,1,1),* into #temp from table_name
这个方法慢。
你要生成99999条临时记录,而 play写的这个,只生成你表中记录数条记录.比如你表里20条,那么它也生成20条。而且还有缺点。当syscolumns表记录少时,生成不了足够记录数,就无法生成足够大的IDENTITY的ID值。
而play那种写法,无论你表中编号有多大,他总能在你的记录数内找到最小断号。
比如你表中只有三条记录,
00005,00007,00999,最大编号是 00999,他不需要生成999条记录,只要生成三条记录,他同样能找出哪个是最小断号。
(
id int
)
Insert into t1Select 00001
union
Select 00002
union
Select 00004
union
Select 00005
union
Select 00006
union
Select 00008Select min(id) From T1 a Where not exists (Select id From T1 b Where b.id =a.id + 1)