T-SQL 实现排序的算法

[闲游四疯]

create table #Instrument(InstrumentCode varchar(20),Orderln int)
insert into #Instrument(InstrumentCode,Orderln)
select '03',1
union all
select '0301',1
union all
select '0302',3
union all
select '0303',4
union all
select '030102',1
union all
select '030101',2
union all
select '030202',1
union all
select '030201',2
union all
select '030203',3
union all
select '030301',1
union all
select '030302',2
union all
select '03010102',1
union all
select '03010101',2
union all
select '03010103',3
union all
select '03010104',4
union all
select '03010201',1
union all
select '03010202',2
union all
select '03010203',3
union all
select '03020101',1
union all
select '03020102',2
union all
select '03020202',1
union all
select '03020201',2
union all
select '03020301',1
union all
select '03020302',2
union all
select '03020303',3

create table #temp(InstrumentCode varchar(20),OrderId bigint)

;with w as
(select a.InstrumentCode,Parent=a1.InstrumentCode
,leve=1,ParentOrder=a1.Orderln
from #Instrument as a
inner join #Instrument as a1
on left(a.InstrumentCode,len(a.InstrumentCode)-2)=a1.InstrumentCode
union all
select w.InstrumentCode,b.InstrumentCode,leve=w.leve+1
,b.Orderln
from w
inner join #Instrument as b
on left(w.InstrumentCode,len(w.InstrumentCode)-(w.leve+1)*2)=b.InstrumentCode
where len(w.InstrumentCode)>((w.leve+1)*2)
)
insert into #temp(InstrumentCode,OrderId)
select w1.InstrumentCode,ww.ParentOrder+w1.Orderln
from #Instrument as w1
join Instrument ins on w1.InstrumentCode=ins.InstrumentCode
outer apply(select w.InstrumentCode
,sum(POWER(100,w.leve)*w.ParentOrder) as ParentOrder
from w
where w1.InstrumentCode=w.InstrumentCode
group by w.InstrumentCode
) as ww
option(MAXRECURSION 0)

select *
from #temp

drop table #Instrument
drop table #temp

每两位code表示一个层，OrderLn 是单个分支里面的同一个层的排序顺序。
sum(POWER(100,w.leve)*w.ParentOrder)
如果层次太多的话产生越界
有什么好的算法可以既避免越界，又可以实现同一层次的全部进行排序

[闲游四疯]

create table #Instrument(InstrumentCode varchar(20),Orderln int)
insert into #Instrument(InstrumentCode,Orderln)
select '03',1
union all
select '0301',1
union all
select '0302',3
union all
select '0303',4
union all
select '030102',1
union all
select '030101',2
union all
select '030202',1
union all
select '030201',2
union all
select '030203',3
union all
select '030301',1
union all
select '030302',2
union all
select '03010102',1
union all
select '03010101',2
union all
select '03010103',3
union all
select '03010104',4
union all
select '03010201',1
union all
select '03010202',2
union all
select '03010203',3
union all
select '03020101',1
union all
select '03020102',2
union all
select '03020202',1
union all
select '03020201',2
union all
select '03020301',1
union all
select '03020302',2
union all
select '03020303',3

create table #temp(InstrumentCode varchar(20),OrderId bigint)

;with w as
(select a.InstrumentCode,Parent=a1.InstrumentCode
 ,leve=1,ParentOrder=a1.Orderln
 from #Instrument as a 
 inner join #Instrument as a1 
 on left(a.InstrumentCode,len(a.InstrumentCode)-2)=a1.InstrumentCode
 union all 
 select w.InstrumentCode,b.InstrumentCode,leve=w.leve+1
 ,b.Orderln
 from w 
 inner join #Instrument as b 
 on left(w.InstrumentCode,len(w.InstrumentCode)-(w.leve+1)*2)=b.InstrumentCode
 where len(w.InstrumentCode)>((w.leve+1)*2)
)
,w1 as
(select w2.InstrumentCode
	,isnull((select dbo.FunPadChar(w.ParentOrder,'L','0',4)
  from w
	WHERE w.InstrumentCode=w2.InstrumentCode
	ORDER BY W.leve DESC
	for xml path('')),'')
	+dbo.FunPadChar(w2.Orderln,'L','0',4) as OrderS
from #Instrument as w2
)
,wR as
(select InstrumentCode,OrderS
 ,ROW_NUMBER() OVER(order by OrderS asc) as ln
 from w1
)
insert into #temp(InstrumentCode,OrderId)
select InstrumentCode,ln
from wR 

option(MAXRECURSION 0)

select *
from #temp

drop table #Instrument
drop table #temp