我正在寻找一种方法来订购mysql结果的值的值不为null的计数。因此,
[id] [1] [1] [0] [1] [1] = 4
[id] [0] [1] [1] [1] [0] = 3
1] [1] = 2[id] [1] [0] [0] [0] [0] = 1
在上面的例子中,我忽略了ID列,但在实践中我不在乎。 ID总是不为NULL,所以将它添加到count不会改变结果。
任何人都有任何想法,不涉及对结果进行PHP解析到一个新的数组?我试图将处理部分保留在数据库级别。
解决方案ORDER BY IF(`a` IS NULL,0,1)+ IF(`b` IS NULL,0,1)... DESC
其中 a , b ,...是字段的名称你需要手动枚举它们)
PS:如果你不知道 0 和 NULL 这:
ORDER BY`a`+`b` 。DESC对你来说已经够好了
I'm looking at a way to order the mysql results by a count of the columns where the value is not null. Therefor,
[id] [1] [1] [0] [1] [1] = 4
[id] [0] [1] [1] [1] [0] = 3
[id] [0] [0] [0] [1] [1] = 2
[id] [1] [0] [0] [0] [0] = 1
In the above case I'm ignoring the ID column but in practice I wouldn't care. ID is always NOT NULL so adding it to count wouldn't change the results.
Anyone have any idea on this that doesn't involve doing a PHP parse on the result into a new array? I'm trying to keep the processing portion in the DB level.
解决方案 ORDER BY IF(`a` IS NULL, 0, 1) + IF(`b` IS NULL, 0, 1) ... DESCWhere a, b, ... is the names of fields (yes, you need to enumerate them all manually)
PS: if you don't know the difference between 0 and NULL this:
ORDER BY `a` + `b` ... DESCwill be good enough for you
更多推荐
“order by”count(columns not null)“
发布评论