sql - 当多列数据在某些列中为空时，如何获取单行数据？

Question

当多列数据中有些列为null时，如何获取单行数据？以下是场景

col1    col2   col3  col4
----- ------ --------------- 
1      NULL    NULL  NULL
NULL   2       NULL  NULL
NULL   NULL    3     NULL
NULL   NULL    NULL  4

I want output like this

col1    col2   col3  col4
----- ------ ---------------
1      2        3    4

Answer 1

您可以使用如下聚合函数:

select min(col1) as col1,min(col2) as col2,min(col3) as col3,min(col4) as col4 from t 
select max(col1) as col1,max(col2) as col2,max(col3) as col3,max(col4) as col4 from t 
select sum(col1) as col1,sum(col2) as col2,sum(col3) as col3,sum(col4) as col4 from t 
select avg(col1) as col1,avg(col2) as col2,avg(col3) as col3,avg(col4) as col4 from t 

然而Min或Max在这种情况下还是比Avg和Sum更有意义。