按最后2个字符的字符串排序 [英] Order by last 2 characters string

问题描述

这是我查询的结果，但排序不正确.我想按最后2个字符排序.结果应为:Fa0/9以下的Fa0/10.

This is the result of my query, but it doesn't order correctly. I want to order by the last 2 characters. The result should be: Fa0/10 below Fa0/9.

Fa0/1
Fa0/10
Fa0/11
Fa0/12
Fa0/2
Fa0/3
Fa0/4
Fa0/5
Fa0/6
Fa0/7
Fa0/8
Fa0/9
Gi0/1
Gi0/2
Null0
Vlan1

我的查询:

SELECT inft.port FROM interfaces AS intf ORDER BY RIGHT(intf.port + 0, 2)

第二个: sqlfiddle

推荐答案

尝试一下:

SELECT port 
FROM interfaces 
ORDER BY SUBSTRING_INDEX(port, '/', 1), CAST(SUBSTRING_INDEX(port, '/', -1) AS SIGNED)

检查> SQL FIDDLE DEMO

输出

|   PORT |
|--------|
|  Fa0/1 |
|  Fa0/2 |
|  Fa0/3 |
|  Fa0/4 |
|  Fa0/5 |
|  Fa0/6 |
|  Fa0/7 |
|  Fa0/8 |
|  Fa0/9 |
| Fa0/10 |
| Fa0/11 |
| Fa0/12 |
|  Gi0/1 |
|  Gi0/2 |
|  Null0 |
|  Vlan1 |

这篇关于按最后2个字符的字符串排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！