我要查询的数据有这样的一列
2G
1G
16G
其他
4G
我想要将这些排序成这样
1G
2G
4G
16G
其他
sql排序语句该怎么写?
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (nn varchar(4))
insert into #tb
select '2G' union all
select '1G' union all
select '16G' union all
select '其他' union all
select '4G'
select * from #tb order by len(replace(nn,'G','')),replace(nn,'G','')
nn
----
1G
2G
4G
16G
其他
(5 行受影响)
select * from tb order by cast(replace(nn,'G','') as int),case when nn='其他' then 1 else 0 end
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (nn varchar(4))
insert into #tb
select '2G' union all
select '1G' union all
select '16G' union all
select '其他' union all
select '4G'
select * from #tb
order by
cast(case when substring(nn,1,patindex('%[^0-9]%',nn)-1)='' then '1000'
else substring(nn,1,patindex('%[^0-9]%',nn)-1) end as int)
nn
----
1G
2G
4G
16G
其他
(5 行受影响)