mysql - 排序并返回具有最大非空值列的行 - MYSQL

Question

我已经在 Google、Stackoverflow 和许多其他网站上搜索了 2 天。我无法提出它的逻辑。

我有 5 列。

col1 |col2  |col3  |col4  |  col52000 | null | 1000 | null | null5000 | 1000 | null | null | nullnull | null | null | null | null1000 | 100  | 250  | 600  | 1114000 | 400  | 350  | null | 111

Sorry for the messed up table above. Still new at stackoverflow.

Now, I want to write a select query which will do the following:
1) Arrange these 5 rows in such a way that the rows with the maximum not null values will appear first. And, gradually it ends with a row of maximum null values.
2) Not return a row where all the columns are null.

For the above example, we should get row4 first then row5 then row1 then row2. Note that row3 is not returned as all the values are null.

Till now I have tried the query below which has come close to solving it but its not exact enough.

SELECT * 
FROM   table 
WHERE  col1 IS NOT NULL 
        OR col2 IS NOT NULL 
        OR col3 IS NOT NULL 
        OR col4 IS NOT NULL 
        OR col5 IS NOT NULL 
ORDER  BY CASE 
            WHEN col1 IS NULL THEN 1 
            ELSE 0 
          END, 
          col1 DESC, 
          CASE 
            WHEN col2 IS NULL THEN 1 
            ELSE 0 
          END, 
          col2 DESC, 
          CASE 
            WHEN col3 IS NULL THEN 1 
            ELSE 0 
          END, 
          col3 DESC, 
          CASE 
            WHEN col4 IS NULL THEN 1 
            ELSE 0 
          END, 
          col4 DESC, 
          CASE 
            WHEN col5 IS NULL THEN 1 
            ELSE 0 
          END, 
          col5 DESC 

Answer 1

最简单的方法是统计空值的个数:

select t.*
from t
where (col1 is not null) or (col2 is not null) or (col3 is not null) or
      (col4 is not null) or (col5 is not null)
order by (col1 is not null) + (col2 is not null) + (col3 is not null) +
         (col4 is not null) + (col5 is not null);

这使用将 bool 值视为整数的 MySQL 快捷方式。