I am using SQL server(MySQL Ver 14.14 Distrib 5.7.21, for Linux (x86_64)). I want to sort data like this.
我正在使用SQL服务器(MySQL Ver 14.14 Distrib 5.7.21,Linux(x86_64))。我想对这样的数据进行排序。
DocTyp-2649
DocTyp-2650
DocTyp-2651
DocTyp-2652
DocTyp-26036
DocTyp-26037
DocTyp-26038
my query is
我的疑问是
SELECT doc_unique_id FROM docs ORDER BY doc_unique_id ASC
my result is
我的结果是
DocTyp-26036
DocTyp-26037
DocTyp-26038
DocTyp-2649
DocTyp-2650
DocTyp-2651
DocTyp-2652
what can u do?
你能做什么?
4 个解决方案
#1
1
Try this query
试试这个查询
SELECT doc_unique_id
FROM docs
ORDER BY cast(replace(doc_unique_id, 'DocTyp-', '') as int)
#2
1
You could explicitly sort the data by using substring()
function (SQL Server) to get the numerical data
您可以使用substring()函数(SQL Server)显式排序数据以获取数值数据
select *
from table
ORDER BY
cast(substring(doc_unique_id, charindex('-', doc_unique_id)+1, len(doc_unique_id)) as int)
#3
1
Try this:
尝试这个:
select doc_unique_id from docs ORDER BY CAST(SUBSTRING_INDEX(doc_unique_id, '-', -1) AS UNSIGNED) ASC;
从文档中选择doc_unique_id ORDER BY CAST(SUBSTRING_INDEX(doc_unique_id,' - ', - 1)AS UNSIGNED)ASC;
#4
0
ORDER BY length(doc_unique_id), doc_unique_id ASC
ORDER BY长度(doc_unique_id),doc_unique_id ASC
#1
1
Try this query
试试这个查询
SELECT doc_unique_id
FROM docs
ORDER BY cast(replace(doc_unique_id, 'DocTyp-', '') as int)
#2
1
You could explicitly sort the data by using substring()
function (SQL Server) to get the numerical data
您可以使用substring()函数(SQL Server)显式排序数据以获取数值数据
select *
from table
ORDER BY
cast(substring(doc_unique_id, charindex('-', doc_unique_id)+1, len(doc_unique_id)) as int)
#3
1
Try this:
尝试这个:
select doc_unique_id from docs ORDER BY CAST(SUBSTRING_INDEX(doc_unique_id, '-', -1) AS UNSIGNED) ASC;
从文档中选择doc_unique_id ORDER BY CAST(SUBSTRING_INDEX(doc_unique_id,' - ', - 1)AS UNSIGNED)ASC;
#4
0
ORDER BY length(doc_unique_id), doc_unique_id ASC
ORDER BY长度(doc_unique_id),doc_unique_id ASC