获取具有最高行长度的数据MYSQL

I'm trying to select the top 20 rows from a MYSQL table (I'm very new to MYSQL and I'm not even sure if it can be called a table). I have a few more conditions which I was able to meet by using inner join, not between etc. After all the processing, I have a table like below

我正在尝试从MYSQL表中选择前20行（我对MYSQL很新，我甚至不确定它是否可以称为表）。我还有一些条件，我可以通过内部连接来满足，而不是之间等等。经过所有处理，我有一个如下表

name,h1,h2,h3
a   , 2, 4, 5
b   ,  ,54, 5
c   ,65, 7, 8
d   , 6,  , 3
e   , 7,  ,
f   ,-1, 3,-5

',,' indicates there's no data for that combination. I prefer having something like 'NA' there The main thing is, I want to select the top 20 rows with highest length. By length I mean

',,'表示该组合没有数据。我更喜欢那里有“NA”之类的东西主要的是，我想选择最长的前20行。我的意思是长度

name,length

a,3
b,2
c,3
d,2
e,1
f,3

I don't need the above table, but based on the length, I need the top 20 rows. I could do it using R but the table is really huge and I'd like to process it in MYSQL and then export the table to my computer. Any help would be highly appreciated.

我不需要上面的表，但根据长度，我需要前20行。我可以使用R来做，但表格非常庞大，我想在MYSQL中处理它，然后将表格导出到我的计算机上。任何帮助将受到高度赞赏。

2 个解决方案

#1

If you want NA for the columns with no values, you can use IFNULL(col, 'NA')

如果你想要NA没有值的列，你可以使用IFNULL（col，'NA'）

select name , h1,h2,h3 
from table 
order by   LENGTH(CONCAT(h1,h2,h3))  desc
limit 20

#2

If I understand "length" correctly, you mean the number of non-NUL values. If so, you can calculate this as:

如果我正确理解“长度”，则表示非NUL值的数量。如果是这样，您可以将其计算为：

select t.*
from table t
order by (h1 is not null) + (h2 is not null) + (h3 is not null) desc
limit 20;

MySQL treats boolean expressions integers, with true being 1 and false being 0. So, the expression in the order by counts the number of non-NULL values. It then orders the result in descending order and chooses the 20 rows with the most non-NULL values.

MySQL处理布尔表达式整数，其中true为1，false为0.因此，顺序中的表达式计算非NULL值的数量。然后它按降序对结果进行排序，并选择具有最多非NULL值的20行。

#1