用字符替换后转类型进行排序
;withtas(
select1asid,1asrev,''ascontent,'z01'asstockNO
unionallselect2,1,'','z02'
unionallselect1,2,'','z03'
unionallselect2,2,'','z04'
unionallselect1,3,'','z05'
)
select*fromt
orderby(casewhenid=1then0-convert(int,replace(stockNO,'z',''))elseconvert(int,replace(stockNO,'z',''))end)
或者先排序,得到排序序号后,再次排序
;withtas(
select1asid,1asrev,''ascontent,'z01'asstockNO
unionallselect2,1,'','z02'
unionallselect1,2,'','z03'
unionallselect2,2,'','z04'
unionallselect1,3,'','z05'
)
select*from(
select*,row_number()over(orderbystockNO)asrowidfromt
)a
orderby(casewhenid=1then0-rowidelserowidend) |