“order by”count（columns not null）“ [英] Order by "count(columns not null)"

问题描述

我正在寻找一种方法来订购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) ... DESC

Where 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` ... DESC

will be good enough for you

这篇关于“order by”count（columns not null）“的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！