問題描述
目前我正在做一個(gè)非?；镜?OrderBy 語(yǔ)句.
Currently I am doing a very basic OrderBy in my statement.
SELECT * FROM tablename WHERE visible=1 ORDER BY position ASC, id DESC
這里的問題是位置"的 NULL 條目被視為 0.因此，所有位置為 NULL 的條目都出現(xiàn)在 1、2、3、4 的條目之前.例如:
The problem with this is that NULL entries for 'position' are treated as 0. Therefore all entries with position as NULL appear before those with 1,2,3,4. eg:
NULL, NULL, NULL, 1, 2, 3, 4
有沒有辦法實(shí)現(xiàn)以下排序:
Is there a way to achieve the following ordering:
1, 2, 3, 4, NULL, NULL, NULL.
推薦答案
MySQL 有一個(gè)未公開的語(yǔ)法來最后對(duì)空值進(jìn)行排序.在列名前放置一個(gè)減號(hào) (-) 并將 ASC 切換為 DESC:
MySQL has an undocumented syntax to sort nulls last. Place a minus sign (-) before the column name and switch the ASC to DESC:
SELECT * FROM tablename WHERE visible=1 ORDER BY -position DESC, id DESC
它本質(zhì)上是 position DESC 的逆，將 NULL 值放在最后，但其他方面與 position ASC 相同.
It is essentially the inverse of position DESC placing the NULL values last but otherwise the same as position ASC.
這里有一個(gè)很好的參考http://troels.arvin.dk/db/rdbms#select-order_by
這篇關(guān)于MySQL 按一個(gè)數(shù)字排序，最后為空的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！