34,590
社区成员
发帖
与我相关
我的任务
分享
create function [dbo].fn_GetAmbNoRange(@Model int)
returns varchar
as
begin
declare @ambNoRange varchar(4000)
declare @amb_no_next varchar(5)
declare @amb_no_restone varchar(5)
declare @No int
declare @Num int
set @ambNoRange=''
set @amb_no_next=''
set @amb_no_restone=''
set @No=0
set @Num=0
declare curNo cursor local for
select substring(A.No,2,len(amb_no))as Num,A.No
from A
where A.Model = @Model
order by cast(substring(A.No,2,len(A.No)) as int)
open curNo
fetch next from curNo into @No,@amb_no_next
while(@@FETCH_STATUS = 0)
begin
if(@Num = @No)
begin
set @ambNoRange=replace(@ambNoRange,'-'+@amb_no_restone,'')
set @ambNoRange=@ambNoRange+'-'+@amb_no_next
end
else
begin
if @ambNoRange=''
set @ambNoRange=@amb_no_next
else
set @ambNoRange=@ambNoRange+','+@amb_no_next
end
set @Num = @No+1
set @amb_no_restone=@amb_no_next
fetch next from curNo into @No,@amb_no_next
end
return @ambNoRange
end
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table # (id int)
insert into #
select 1 union all
select 3 union all
select 4 union all
select 5 union all
select 7
declare @str varchar(100)
;
with gid(gid,id) as
(
select id - row_number()over(order by id), id from # -- where Model=@Model
), val(val) as
(
select ltrim(min(id)) + isnull('-'+ltrim(nullif(max(id),min(id))),'') from gid group by gid
)
select @str = isnull(@str+',','')+val from val
select @str --> 1,3-5,7