declare @Start nvarchar(10),@End nvarchar(10),@Qty int
select @Start=N'DH90251024',@End=N'DH90252000',@Qty=977;with C
as
(select @Start as Col,left(@Start,4)+cast(right(@Start,6)+10 as nvarchar(6)) Col2,10 as Qty,ID=1
union all
select
left(Col2,4)+cast(right(Col2,6)+1 as nvarchar(6)),left(Col2,4)+cast(right(Col2,6)+10 as nvarchar(6)),Qty=case when (ID+1)*10>@Qty then @Qty-ID*10 else 10 end, ID+1
from C
where Col2<@End and Qty<@Qty)
select top 1 Col,Col2,Qty from C order by newID()
set nocount on
go
create function dbo.fn_split (
@Number char(10)
,@cnt int
,@base int = 10
) returns @tb table (st char(10), en char(10), cnt int)
as begindeclare @prefix char(2) set @prefix = left(@Number,2)declare @pcnt int
declare @numberBase int set @numberBase = cast(right(@Number,8) as int)while @cnt>0 begin
if @cnt <= @base
insert into @tb values (@prefix+cast(@numberBase as char(8)), @prefix+cast(@numberBase+@base-1 as char(8)), @cnt)
else
insert into @tb values (@prefix+cast(@numberBase as char(8)), @prefix+cast(@numberBase+@base-1 as char(8)), @base)
set @numberBase = @numberBase + @base
set @cnt = @cnt - @base
endreturn
end
goselect * from dbo.fn_split ('DH90251024',997,10)
-- st,en,cnt
-- DH90251024,DH90251033,10
-- DH90251034,DH90251043,10
-- DH90251044,DH90251053,10
-- ...
-- DH90252004,DH90252013,10
-- DH90252014,DH90252023,7
INSERT INTO test70 SELECT 'DH90251024','DH90252000',977DECLARE @m int
DECLARE @a table(id int identity(0,1),a int)
DECLARE @b table(id int,a varchar(20),b varchar(20))
DECLARE @c table(a varchar(20),b varchar(20))
SELECT @m=c FROM test70 tSET ROWCOUNT @m
INSERT @a SELECT 0 FROM syscolumns sINSERT @b
SELECT id,
'DH'+ltrim(cast(STUFF(t.a,1,2,'') AS int)+id*10),
'DH'+ltrim(case WHEN cast(STUFF(t.a,1,2,'') AS int)+id*10+9>cast(STUFF(t.b,1,2,'') AS int ) THEN cast(STUFF(t.b,1,2,'') AS int ) ELSE cast(STUFF(t.a,1,2,'') AS int)+id*10+9 END )
FROM test70 t,@a b
WHERE cast(STUFF(t.a,1,2,'') AS int)+id*10<=cast(STUFF(t.b,1,2,'') AS int)DECLARE @Mi int,@Ma int
SELECT @mi=min(id),@Ma=max(id) FROM @bINSERT @c SELECT a,b FROM @b WHERE id in(@mi,@Ma)
INSERT @c SELECT TOP 8 a,b FROM @b WHERE id NOT in(@Mi,@Ma) ORDER BY NEWID()
SELECT * FROM @c
还有就是DH90251024是不定长的,还有的是HKD000568Z这样的
比如:
DH90251024 DH90251024 1
........
select * from dbo.fn_split ('DH90251024',10,1)
-- st,en,cnt
-- DH90251024,DH90251024,1
-- DH90251025,DH90251025,1
-- DH90251026,DH90251026,1
-- DH90251027,DH90251027,1
-- DH90251028,DH90251028,1
-- DH90251029,DH90251029,1
-- DH90251030,DH90251030,1
-- DH90251031,DH90251031,1
-- DH90251032,DH90251032,1
-- DH90251033,DH90251033,1
set nocount on
go
drop function dbo.fn_split
go
create function dbo.fn_split (
@Number varchar(10)
,@cnt int
,@base int = 10
) returns @tb table (st char(10), en char(10), cnt int)
as begindeclare @prefix char(2) set @prefix = left(@Number,2)
declare @codelen int set @codelen = 8
declare @zeroleader char(8) set @zeroleader=REPLICATE('0',@codelen)declare @pcnt int
declare @numberBase int set @numberBase = cast(right(@Number,@codelen) as int)while @cnt>0 begin
if @cnt <= @base
insert into @tb values (@prefix+right(@zeroleader+cast(@numberBase as varchar),8)
, @prefix+right(@zeroleader+cast(@numberBase+@cnt-1 as varchar),8)
, @cnt)
else
insert into @tb values (@prefix+right(@zeroleader+cast(@numberBase as varchar),8)
, @prefix+right(@zeroleader+cast(@numberBase+@base-1 as varchar),8)
, @base)
set @numberBase = @numberBase + @base
set @cnt = @cnt - @base
endreturn
end
goselect * from dbo.fn_split ('DH00002000',997,10)
-- st,en,cnt
-- DH00002000,DH00002009,10
-- DH00002010,DH00002019,10
-- ...
-- DH00002970,DH00002979,10
-- DH00002980,DH00002989,10
-- DH00002990,DH00002996,7
select top @s * from table order by newid()
这样@s是错的,还有什么办法能实现这样的效果啊>
update #t set nsno = (select count(1) from #t b where b.[newid]<=a.[newid]) from #t a
select * from #t where nsno<=@s
drop table #t
go
drop function dbo.fn_split
go
create function dbo.fn_split (
@Number varchar(18) , @cnt int, @step int = 10, @codelen int = 7
) returns @tb table (st varchar(16), en varchar(16), cnt int)
as begin
declare @prefix varchar(12)
set @prefix = left(@Number,len(@Number)-@codelen)
declare @zeroleader varchar(18) set @zeroleader=REPLICATE('0',@codelen)
declare @pcnt int, @numberBase int set @numberBase = cast(right(@Number,@codelen) as int)while @cnt>0 begin
if @cnt <= @step
insert into @tb values (@prefix+right(@zeroleader+cast(@numberBase as varchar),@codelen)
, @prefix+right(@zeroleader+cast(@numberBase+@cnt-1 as varchar),@codelen)
, @cnt)
else
insert into @tb values (@prefix+right(@zeroleader+cast(@numberBase as varchar),@codelen)
, @prefix+right(@zeroleader+cast(@numberBase+@step-1 as varchar),@codelen)
, @step)
set @numberBase = @numberBase + @step
set @cnt = @cnt - @step
end
return
end
go