如何按自定义字段排序MySQL ResultSet？

Question

以下是架构和数据：

Create table tblTestSorting (col int); 
INSERT INTO tblTestSorting VALUES(2), (5), (3), (6), (4), (7), (1) 

我有这样的查询：

SELECT col, 5-col AS 'extra' FROM tblTestSorting ORDER BY 'extra'; 

我想通过“额外”的列进行排序的结果，但 此查询不放弃我结果按“额外”列排序。

如何实现此目的？

Answer 1

试试这个：

SELECT col, (5 - col) AS extra 
FROM tblTestSorting 
ORDER BY extra; 

检查SQL FIDDLE DEMO

输出

| COL | EXTRA | 
|-----|-------| 
| 7 | -2 | 
| 6 | -1 | 
| 5 |  0 | 
| 4 |  1 | 
| 3 |  2 | 
| 2 |  3 | 
| 1 |  4 | 

Answer 2

产生由式（ASC或DESC）的顺序.. 试试这个..

SELECT col, 5-col AS extra FROM tblTestSorting ORDER BY col ASC 

Answer 3

两种方式

SELECT col, 5-col AS 'extra' FROM tblTestSorting ORDER BY 5-col; 


SELECT col, 5-col AS 'extra' FROM tblTestSorting ORDER BY 2; 

Answer 4

试试这个：

SELECT col, 5-col AS 'extra' FROM tblTestSorting ORDER BY 'extra' ASC 

或

SELECT * FROM (SELECT col, 5-col AS 'extra' FROM tblTestSorting) as t ORDER BY t.'extra' ASC 

Answer 5

解决方案1 ​​

SELECT * FROM（SELECT关口，5-COL AS“额外'FROM tblTemp）额外订购

或溶液2

SELECT栏，5-COL AS`extra` FROM tblTemp为了通过`extra`