SQL中的
考虑以下结果,其中code
是类型VARCHAR
:
SELECT code
FROM lock
ORDER BY CAST(code AS signed) > 0 DESC, `code
|code|
|4 |
|420 |
|5 |
|T6 |
|X30 |
如何更改查询,使其按以下顺序返回结果:
|code|
|4 |
|5 |
|420 |
|T6 |
|X30 |
SELECT code
FROM lock
ORDER BY CAST(code AS signed) > 0 DESC, CAST(code AS signed) ASC, code ASC
第一个顺序将把数字排到前面。第二个顺序只会将数字升序排列,而第一个顺序会将它们保持在开头。第三种方法只对字符串进行升序排序,数字将保持其顺序,因为它们已经排序。
例如:
SELECT *
, code+0
, code+0=0
FROM (SELECT '4' code UNION
SELECT '420' UNION
SELECT '5' UNION
SELECT 'T6' UNION
SELECT 'X30') n
ORDER
BY code+0=0
, code+0
, code;
+------+--------+----------+
| code | code+0 | code+0=0 |
+------+--------+----------+
| 4 | 4 | 0 |
| 5 | 5 | 0 |
| 420 | 420 | 0 |
| T6 | 0 | 1 |
| X30 | 0 | 1 |
+------+--------+----------+
LENGTH()
函数用于获取字符串的长度:
SELECT code
FROM lock
ORDER BY LENGTH(code), code
输出:
+------+
| code |
+------+
| 4 |
| 5 |
| T6 |
| 420 |
| X30 |
+------+