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)
如果层次太多的话产生越界
有什么好的算法可以既避免越界,又可以实现同一层次的全部进行排序

sql
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问