通过“计数(列不为空)"进行排序 [英] Order by "count (columns not null)"

问题描述

我正在寻找一种通过对值不为null的列进行计数来对MySQL结果进行排序的方法.因此，

I'm looking at a way to order the MySQL results by a count of the columns where the value is not null. Therefore,

[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

在上述情况下，我忽略了ID列，但实际上我不在乎. ID始终不为NULL，因此将其添加到count不会改变结果.

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.

有人对此有任何想法，不涉及对结果进行PHP解析到一个新数组中吗?我正在尝试将处理部分保留在数据库级别.

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

其中a，b，...是字段名称(是的，您需要手动枚举所有字段)

Where a, b, ... is the names of fields (yes, you need to enumerate them all manually)

PS:如果您不知道0和NULL之间的区别，则:

PS: if you don't know the difference between 0 and NULL this:

ORDER BY `a` + `b` ... DESC

对你足够好

这篇关于通过“计数(列不为空)"进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！