首页新闻找找看学习计划

T-SQL 排序的算法怎么实现

0
悬赏园豆:5 [待解决问题]
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) 如果层次太多的话产生越界 有什么好的算法可以既避免越界,又可以实现同一层次的全部进行排序

空紫竹的主页 空紫竹 | 菜鸟二级 | 园豆:204
提问于:2017-10-11 11:09
< >
分享
所有回答(2)
0
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
空紫竹 | 园豆:204 (菜鸟二级) | 2017-10-11 12:49
0

看你code长度是有规律的,能否查询到一个列,然后循环去做排序。

SELECT InstrumentCode

           ,Orderln

           ,LEN(InstrumentCode) AS CodeLenth

 

   FROM #Instrument

查询出来看看对你是否有帮助,不同层的InstrumentCode长度不一样,我们一层一层处理掉。

可以尝试一下游标(性能略差)或者表内循环。

chengeng | 园豆:292 (菜鸟二级) | 2017-10-16 09:58
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册