MySQL PHP订单

Question

我正在编写MySQL查询来对这类数据进行排序

Traffic
100/40
12/1
50/20
25/5

从左手边数中求出的右方数的值。。

此外，我还有多个列进行排序。

前SELECT * FROM fltable ORDER BY Traffic DESC,Speed ASC,Cost ASC

我需要整理那些数据升序和降序，有人能帮我做这个吗？

谢谢

Answer 1

SELECT * FROM fractions;
+-----------+
| fractions |
+-----------+
| 100/40    |
| 12/1      |
| 25/5      |
| 50/20     |
+-----------+

SELECT fractions
     , SUBSTRING_INDEX(fractions,'/',1)/SUBSTRING_INDEX(fractions,'/',-1)x 
  FROM fractions 
 ORDER 
    BY x DESC;
+-----------+------+
| fractions | x    |
+-----------+------+
| 12/1      |   12 |
| 25/5      |    5 |
| 100/40    |  2.5 |
| 50/20     |  2.5 |
+-----------+------+

Answer 2

尝试如下查询：

SELECT *, CONVERT(SUBSTR(var, 1, POSITION('/' IN var) - 1), UNSIGNED INTEGER) as num 
FROM table 
ORDER BY num DESC

在/之前用SUBSTR和CONVERT获取数字到int

使用num列的结果

100
50
25
12