在实际开发中有时会有升序排列id为0的在最后的需求,这里我记录了一种在stack overflow中比较简单的方法如下:
You may want to try the following:
SELECT * FROM your_table ORDER BY your_field = 0, your_field;
Test case:
CREATE TABLE list (a int);
INSERT INTO list VALUES (0);
INSERT INTO list VALUES (0);
INSERT INTO list VALUES (0);
INSERT INTO list VALUES (1);
INSERT INTO list VALUES (2);
INSERT INTO list VALUES (3);
INSERT INTO list VALUES (4);
INSERT INTO list VALUES (5);
Result:
SELECT * FROM list ORDER BY a = 0, a;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 0 |
| 0 |
| 0 |
+------+
8 rows in set (0.00 sec)
参考网址