sql语句中对一个字段排序,字段中含有字符串和数字,(怎根据数字进行排序...

发布网友

我来回答

共3个回答

热心网友

order by 字段名称+0 desc/asc的形式进行排序
order by 字段名称*1 desc/asc的形式进行排序

热心网友

假如表为test，name字段存放班级，代码如下:
select id,name,substring_index(name, '班级',1) as num
from test
ORDER BY num asc

热心网友

按照特殊字符后的数字进行排序：

正序：

select * from table01 where ColumnNum like 'BHxxxx-%' order by cast(SUBSTRING(ColumnNum,CHARINDEX('-',ColumnNum,CHARINDEX('-',ColumnNum))+1,len(ColumnNum)) as int);

倒序：

select * from table01 where ColumnNum like 'BHxxxx-%' order by cast(SUBSTRING(ColumnNum,CHARINDEX('-',ColumnNum,CHARINDEX('-',ColumnNum))+1,len(ColumnNum)) as int) desc;