有一个表,
起始编号 终止编号
0000000000000001 0000000000010000
0000000000010001 0000000000020000希望求出此表起始编号与终止编号对应的所有编号。0000000000000001
0000000000000002
0000000000000003
0000000000000004
……
0000000000010000
0000000000010001
…
0000000000020000此命令如何写?谢谢
起始编号 终止编号
0000000000000001 0000000000010000
0000000000010001 0000000000020000希望求出此表起始编号与终止编号对应的所有编号。0000000000000001
0000000000000002
0000000000000003
0000000000000004
……
0000000000010000
0000000000010001
…
0000000000020000此命令如何写?谢谢
drop table tb
Go
Create table tb([起始编号] nvarchar(16),[终止编号] nvarchar(16))
Insert tb
select N'0000000000000001',N'0000000000010' union all
select N'0000000000010001',N'0000000000020'
Go
If object_id('tempdb..#')is not null drop table #
go
select top 20000 ID=identity(int,1,1) into # from sys.tables a ,sys.tables b ,sys.tables c,sys.objects d
Select right(1000000000000000+ID,8)
from # a,tb b
where ID between cast([起始编号] as int) and cast([终止编号] as int)
insert into tb values('0000000000000001','0000000000010000')
insert into tb values('0000000000010001','0000000000020000')
go--使用临时表完成
SELECT TOP 80000 id = IDENTITY(int, 0, 1) INTO # FROM syscolumns a, syscolumns b ,syscolumns cselect right('000000000000000' + cast(cast(起始编号 as int) + n.id as varchar),16) 所有编号
from tb m, # n
where cast(起始编号 as int) + n.id <= cast(终止编号 as int)
order by 所有编号 drop table tb , #/*
所有编号
--------------------------------
0000000000000001
0000000000000002
0000000000000003
0000000000000004
0000000000000005
0000000000000006
0000000000000007
0000000000000008
0000000000000009
0000000000000010
0000000000000011
0000000000000012
0000000000000013
0000000000000014
0000000000000015
0000000000000016
0000000000000017
0000000000000018
0000000000000019
0000000000000020
0000000000000021
0000000000000022
0000000000000023
0000000000000024
0000000000000025
0000000000000026
0000000000000027
...0000000000019994
0000000000019995
0000000000019996
0000000000019997
0000000000019998
0000000000019999
0000000000020000(所影响的行数为 20000 行)
*/
} 其中fromBase为原来的格式 toBase为将要转换成的格式
对我有用[0] 丢个板砖[0] 引用 举报 管理 TOP 精华推荐:请问如何在代码中实现SQL中的触发器
if not object_id('tb') is null
drop table tb
Go
Create table tb([起始编号] nvarchar(16),[终止编号] nvarchar(16))
Insert tb
select N'0000000000000001',N'0000000000000010' union all
select N'9900090090009001',N'9900090090009010'declare @编号长度 int
set @编号长度=16;with t as
(
select 0 as cnt
union all
select cnt + 1 from t where cnt < 20000
)select right(replicate('0',@编号长度) + ltrim(编号),@编号长度) 编号 from
(
select cast(tb1.起始编号 as numeric(20,0))+t.cnt as 编号
from tb tb1, t
where cast(tb1.起始编号 as numeric(20,0))+t.cnt< cast(tb1.终止编号 as numeric(20,0))
) b
order by b.编号
option (maxrecursion 0)