求一个存储过程//

[it_sql]

DH90251024 DH90252000 977
怎么分解成如下这样的:
DH90251024 DH90251033 10
DH90251034 DH90251043 10
..
DH90251994 DH90252000 7
然后随机选出10组数据，这10组中必须包括首尾两组，就是要有
DH90251024 DH90251033 10 和 DH90251994 DH90252000 7
怎么选择？
用存储过程怎么实现?

[中国风]

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()

[tim_spac]

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 begin

declare @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
end

return 
end
go

select * 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

[chuifengde]

CREATE TABLE test70(a varchar(20),b varchar(20),c int)
INSERT INTO test70 SELECT 'DH90251024','DH90252000',977

DECLARE @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 t

SET ROWCOUNT @m
INSERT @a SELECT 0 FROM syscolumns s

INSERT @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 @b

INSERT @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

[tim_spac]

[Quote=引用 5 楼 it_sql 的回复:]
随机选出10组数据后，怎么把这10组数据有一个一个的分开呢?
比如:
DH90251024  DH90251024  1
........
[/Quote]

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

[tim_spac]

[Quote=引用 4 楼 it_sql 的回复:]
要是把'DH90251024'  'DH90252000 ' 改成'DH00001024','DH00002000'得到的结果就不对了,0不能操作么?
[/Quote]

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 begin

declare @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
end

return 
end
go

select * 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

[tim_spac]

[Quote=引用 4 楼 it_sql 的回复:]
..还有就是DH90251024是不定长的,还有的是HKD000568Z这样的
[/Quote]
需要再引入一个函数用来拆分解析HKD000568Z中的数据部分...

[it_sql]

要是把'DH90251024' 'DH90252000 ' 改成'DH00001024','DH00002000'得到的结果就不对了,0不能操作么?
还有就是DH90251024是不定长的,还有的是HKD000568Z这样的

[it_sql]

随机选出10组数据后，怎么把这10组数据有一个一个的分开呢?
比如:
DH90251024 DH90251024 1
........

[it_sql]

declare @s int set @s=5
select top @s * from table order by newid()
这样@s是错的,还有什么办法能实现这样的效果啊>

[无心雨云]

高手指点
我学习

[tim_spac]

select top 不能跟变量，如果需要得用动态脚本

[it_sql]

那还有没有别的方法实现这样的效果呢?

[tim_spac]

declare @s int set @s=5

select *,[newid]=newid(),nsno=0 into #t from yourtable
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

[it_sql]

我也这么做出来了,呵呵,还是要这么麻烦的,要过五关斩六将啊,..

[tim_spac]

set nocount on
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