MySQL Orderby一个数字，空字符串(或0)，最后一个 [英] MySQL Orderby a number, Empty Strings (or 0's) Last

问题描述

问了一个与此问题非常相似的问题...

Just asked a question pretty similar to this one...

目前，我在声明中执行的是非常基本的OrderBy.

Currently I am doing a very basic OrderBy in my statement.

SELECT * FROM tablename WHERE visible=1 ORDER BY position ASC, id DESC

此问题是位置"的空字符串条目被视为0.因此，所有位置为空字符串的条目都出现在具有1,2,3,4的条目之前.例如:

The problem with this is that empty string entries for 'position' are treated as 0. Therefore all entries with position as empty string appear before those with 1,2,3,4. eg:

'', '', '', 1, 2, 3, 4

或:

0, 0, 0, 1, 2, 3, 4

有没有一种方法可以实现以下排序:

Is there a way to achieve the following ordering:

1, 2, 3, 4, '', '', ''.

或:

1, 2, 3, 4, 0, 0, 0.

我认为该解决方案可能具有某种替换功能，但是我找不到能够满足我要求的功能.

I assume the solution may have some kind of replace function but I am not able to find a function which does what I am after.

推荐答案

SELECT * 
FROM tablename 
WHERE visible=1 
ORDER BY 
    case when position in('', '0') then 1 else 0 end,
    position ASC, 
    id DESC

这篇关于MySQL Orderby一个数字，空字符串(或0)，最后一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！