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

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)
)
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```

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
from w
WHERE w.InstrumentCode=w2.InstrumentCode
ORDER BY W.leve DESC
for xml path('')),'')
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```

0

SELECT InstrumentCode

,Orderln

,LEN(InstrumentCode) AS CodeLenth

FROM #Instrument

chengeng | 园豆：294 (菜鸟二级) | 2017-10-16 09:58

您需要登录以后才能回答，未注册用户请先注册