T-SQL 排序的算法怎么实现不会遇到越界的问题

Question

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

Answer 1

参考GPT和自己的思路：

针对这个问题，可以使用字符串拆分和排序的方式进行排序。首先，将 InstrumentCode 按照每两个字符拆分成一个小分支，并存储每个小分支的信息，然后在每个小分支内部按照 OrderLn 进行排序。接下来，构建一个以每个小分支为节点的树状结构，每个节点的权值为其所有父节点的权值与 OrderLn 的乘积和。最后，对这个树状结构进行深度优先遍历，按照节点的权值进行排序输出即可。这种算法可以避免越界，同时也考虑到了同一层次的排序问题。具体的实现可以参考下面的示例代码：

-- 构建按照每两个字符拆分成的小分支  
;WITH cte AS  
(  
    SELECT  
        InstrumentCode = LEFT(InstrumentCode,LEN(InstrumentCode) - 2),  
        Sub = RIGHT(InstrumentCode,2),  
        OrderLn  
    FROM  
        #Instrument  
)  
-- 对每个小分支进行排序  
, cteSorted AS  
(  
    SELECT  
        InstrumentCode,  
        Sub,  
        OrderLn,  
        ROW_NUMBER() OVER(PARTITION BY InstrumentCode ORDER BY OrderLn) AS RowNum  
    FROM  
        cte  
)  
-- 构建树状结构  
, cteTree AS  
(  
    SELECT  
        InstrumentCode,  
        Sub,  
        [Level] = ROW_NUMBER() OVER (ORDER BY InstrumentCode ASC),  
        OrderLn,  
        ParentCode = NULL  
    FROM  
        cteSorted  
    WHERE  
        RowNum = 1  
    UNION ALL  
    SELECT  
        cteSorted.InstrumentCode,  
        cteSorted.Sub,  
        [Level] = cteTree.[Level] + 1,  
        cteSorted.OrderLn,  
        ParentCode = cteTree.Sub + '.' + cteTree.ParentCode  
    FROM  
        cteSorted  
        INNER JOIN cteTree ON cteTree.InstrumentCode = cteSorted.InstrumentCode  
    WHERE  
        cteSorted.RowNum = 1 OR cteTree.[Level] = cteSorted.RowNum - 1  
)  
-- 计算节点权值  
, cteWeight AS  
(  
    SELECT  
        InstrumentCode,  
        Sub,  
        [Level],  
        Weight = SUM(OrderLn * POWER(100,[Level]))  
    FROM  
        cteTree  
    GROUP BY  
        InstrumentCode,  
        Sub,  
        [Level]  
)  
-- 深度优先遍历并排序输出  
SELECT  
    t.InstrumentCode,  
    t.Sub  
FROM  
    cteTree AS t  
    INNER JOIN cteWeight AS w ON t.InstrumentCode = w.InstrumentCode AND t.Sub = w.Sub  
ORDER BY  
    w.Weight  

总体的思路就是将 InstrumentCode 按照每两个字符进行拆分并排序，在构建树结构并计算节点权值后进行深度优先遍历。在这个过程中，只需要维护每个节点的 InstrumentCode 和 Sub 信息即可，无需考虑越界的问题。