MySQL查询在单行中计算非空值 [英] MySQL query to count non-null values in a single row

问题描述

我正在尝试汇总一个MYSQL查询，该查询将对单行中选择字段中的非null(或更好的是，非零)值的数量进行计数，然后从最低到最高(基于数数).例如，我有一个包含5个字段的表... ID，名称，Score_1，Score_2，Score_3.我想计算每条记录在Score_1，Score_2和Score_3中值"0"存在多少次，然后从大多数非零值到最小值进行排序.

I'm trying to put together a MYSQL query that will count the number of Non-Null (or better yet, non-zero) values in select fields in a single row and then sort from lowest to highest (based on the count). For example, I have a table with 5 fields... ID, Name, Score_1, Score_2, Score_3. I want to count how many times the value "0" exists in Score_1, Score_2 and Score_3 for each record, then sort from most non zero values to least.

ID   Name   Score_1   Score_2   Score_3
1    Dan    8         7         0
2    Joe    0         0         3
3    Chris  0         0         0
4    Mike   4         5         5

我认为查询必须看起来像这样...

I assume the query has to look something like this...

选择ID，名称，Score_1，Score_2，Score_3，其中(???)ORDER BY(???)

Select ID, Name, Score_1, Score_2, Score_3 where (???) ORDER BY (???)

输出应如下所示(由于ID 4的非零条目数量最少，因此将首先显示ID 4)...

Output should look like this (ID 4 is displayed first since it has the least amount of non-zero entries)...

ID   Name   Score_1   Score_2   Score_3
4    Mike   4         5         5
1    Dan    8         7         0
2    Joe    0         0         3
3    Chris  0         0         0

我对mysql查询有点陌生，所以任何帮助将不胜感激.我以为COUNT函数会有所帮助，但是该函数似乎可以统计所有行中的列.也许有一种方法可以使用COUNT函数并将其限制为单行，以便可以按该行数进行排序?

I'm somewhat new to mysql query's, so any help would be greatly appreciated. I thought the COUNT function would help, but that function appears to count columns from all rows. Perhaps there is a way to use the COUNT function and limit it to a singel row so it can be sorted by that row count?

推荐答案

这应该做您想要的:

SELECT ID, Name, Score_1, Score_2, Score_3
FROM Table1
ORDER BY (Score_1 = 0) + (Score_2 = 0) + (Score_3 = 0)

结果:

ID  Name   Score_1  Score_2  Score_3
4   Mike   4        5        5      
1   Dan    8        7        0      
2   Joe    0        0        3      
3   Chris  0        0        0      

这篇关于MySQL查询在单行中计算非空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！