我的sql用数字部分对varchar字段进行排序 [英] My sql sorting varchar field with numeric part

问题描述

我有一个表 data_table .我需要从表中获取所有标题.标题字段(类型 varchar)包含如下所示的字母数字数据

Hi I have a table data_table .I need to get all the title from the table.The title field (type varchar) contain alphanumeric data like below

title
---------
ab 2007
ab 2017
ba 2018
ba 2017
cd 2017
cd 2016

我想要的输出是

 title
---------
 ba 2018
 ab 2017
 ba 2017
 cd 2017
 cd 2016
 ab 2007

我通过 CAST 尝试过.但没有得到所需的结果

I tried by CAST .But didnt get required result

SELECT title, CAST(title as SIGNED) AS casted_column FROM data_table ORDER BY `title` DESC

推荐答案

使用 RIGHT 获取最后 4 位数字，然后将其转换为无符号并排序

Use RIGHT to get last 4 digits and then cast it to unsigned and sort it

SELECT title 
from tbl
order by cast(right(title,4) as unsigned) desc, title desc

这篇关于我的sql用数字部分对varchar字段进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！