sql - PostgreSQL 查询列上空值最少的行

Question

如何查询输出为列中空值最少的行的行？

我的数据是:

ID         | col1     | col2      | col3      | col4     
-----------+----------+-----------+-----------+-----------
 1         | Null     |Null       | with value| with value
 2         |with value|Null       | with value| with value
 3         |with value|Null       | Null      | Null       

结果会是:

 ID         | col1     | col2      | col3      | col4     
 -----------+----------+-----------+-----------+-----------
  2         |with value|Null       | with value| with value  

因为 id 2 是空值最少的记录。任何帮助将不胜感激。谢谢

Answer 1

您可以:

1. 按空值数(升序)对行进行排序
2. 将行限制为 1 (LIMIT 1)

您的代码:

SELECT *
FROM your_table
ORDER BY 
    CASE WHEN col1 IS NULL THEN 1 ELSE 0 END +
    CASE WHEN col2 IS NULL THEN 1 ELSE 0 END +
    CASE WHEN col3 IS NULL THEN 1 ELSE 0 END +
    CASE WHEN col4 IS NULL THEN 1 ELSE 0 END 
LIMIT 1