每条数据都要最新一条有值的数据去重

现在我有三行数据，我想获取每个字段最新一条并且有值的数据，最后的结果应该是黄色的那一行

这种SQL该怎么去写呢？

案例数据如下

create table tbl1(
id varchar2(10),
name varchar2(10),
age varchar2(5),
address varchar2(10),
phone varchar2(20),
"date" varchar2(20)
);


insert into tbl1 values('1','lisa','16','','188xxxxxxxx','20230105');
insert into tbl1 values('1','lisa','17','CHINA','','20230106');
insert into tbl1 values('1','lisa','','','','20230107');

因为我们要的每个列的最新的一条“有值”的数据，并且把这三条聚合成一条数据。

那么就可以先根据主键进行分组，然后每一列去row_number()开窗，再加上一个限定条件不能为空，每一列换成一个子查询的方式来就可以解决这个问题了

办法SQL如下

 select 
       (select id 
       from (
            (select a.*,row_number() over(partition by id order by "date" desc) as idrn 
        from tbl1 a 
        where id is not null)
        )a where main.id = a.id 
        and idrn = 1)                                           as id,
        (select name 
       from (
            (select a.*,row_number() over(partition by id order by "date" desc) as namern 
        from tbl1 a 
        where name is not null)
        )b where main.id = b.id 
        and namern = 1)                                         as name,
         (select age 
       from (
            (select a.*,row_number() over(partition by id order by "date" desc) as agern 
        from tbl1 a 
        where age is not null)
        )b where main.id = b.id 
        and agern = 1)                                          as age,
        (select address 
       from (
            (select a.*,row_number() over(partition by id order by "date" desc) as addressrn 
        from tbl1 a 
        where address is not null)
        )b where main.id = b.id 
        and addressrn = 1)                                          as address,
         (select phone 
       from (
            (select a.*,row_number() over(partition by id order by "date" desc) as phonern 
        from tbl1 a 
        where phone is not null)
        )b where main.id = b.id 
        and phonern = 1)                                          as phone,
        (select "date" 
       from (
            (select a.*,row_number() over(partition by id order by "date" desc) as datern 
        from tbl1 a 
        where phone is not null)
        )b where main.id = b.id 
        and datern = 1)                                          as "date"
 from tbl1 main
 group by id