我有一列包括数字(作为字符串(,字符串和零。我想按数值对所有数字进行排序,然后按字母顺序排列字符串,然后按另一列排序的所有 nils 和空字符串。
所以,给定:
+----+-----+------+
| id | val | name |
+----+-----+------+
| 0 |bbb | |
| 1 |aaa | |
| 2 |0920 | |
| 3 |320 | |
| 4 | |c |
| 5 |NULL |b |
| 6 | |a |
+----+-----+------+
我想要:
+----+-----+------+
| id | val | name |
+----+-----+------+
| 3 |320 | |
| 2 |0920 | |
| 1 |aaa | |
| 0 |bbb | |
| 6 | |a |
| 5 |NULL |b |
| 4 | |c |
+----+-----+------+
谢谢!
SqlFiddleDemo
首先使用CASE
创建组,数字在前,空在后,其余在中间
然后按数值和名称排序
SELECT *
FROM Table1
ORDER BY CASE WHEN `val` REGEXP '[0-9]+' THEN 1
WHEN `val` IS NULL THEN 3
ELSE 2
END,
CASE WHEN `val` REGEXP '[0-9]+' THEN CONVERT(`val`, SIGNED INTEGER)
ELSE 0
END,
name
输出
| id | val | name |
|----|--------|--------|
| 3 | 320 | (null) |
| 2 | 0920 | (null) |
| 0 | bbb | (null) |
| 1 | aaa | (null) |
| 6 | (null) | a |
| 5 | (null) | b |
| 4 | (null) | c |