Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t1 (
  uname varchar2(20)
, paystat int
, paydate date
);
insert all 
into t1 values ('林佳穎', 0, to_date('2022-07-13 15:30:01', 'YYYY-MM-DD HH24:MI:SS'))
into t1 values ('林佳穎', 1, to_date('2022-07-14 10:10:01', 'YYYY-MM-DD HH24:MI:SS'))
into t1 values ('翁翠霙', 0, to_date('2022-07-13 14:10:00', 'YYYY-MM-DD HH24:MI:SS'))
into t1 values ('劉瑞豐', 0, to_date('2022-07-13 11:40:02', 'YYYY-MM-DD HH24:MI:SS'))
select 1
  from dual;
4 rows affected

select *
  from t1;


UNAME
PAYSTAT
PAYDATE


林佳穎
0
13-JUL-22

林佳穎
1
14-JUL-22

翁翠霙
0
13-JUL-22

劉瑞豐
0
13-JUL-22


select a.uname
     , decode(greatest(a.paystat,nvl(b.paystat, 0)), 1, '已繳', '未繳') as chkpay
     , decode(greatest(a.paystat,nvl(b.paystat, 0)), 1
       , to_char(b.paydate, 'YYYY-MM-DD HH24:MI:SS')
       , to_char(a.paydate, 'YYYY-MM-DD HH24:MI:SS')
       ) as paydate
  from (select uname, paystat, paydate
          from t1
         where paystat = 0) a
  left outer join
       (select uname, paystat, paydate
          from t1
         where paystat = 1) b
    on a.uname = b.uname;
        


UNAME
CHKPAY
PAYDATE


林佳穎
已繳
2022-07-14 10:10:01

劉瑞豐
未繳
2022-07-13 11:40:02

翁翠霙
未繳
2022-07-13 14:10:00


select uname
     , decode(max(paystat),1, '已繳', '未繳') as chkpay
     , to_char(max(paydate), 'YYYY-MM-DD HH24:MI:SS') as paydate
  from t1
 group by uname;


UNAME
CHKPAY
PAYDATE


林佳穎
已繳
2022-07-14 10:10:01

翁翠霙
未繳
2022-07-13 14:10:00

劉瑞豐
未繳
2022-07-13 11:40:02

UNAME	PAYSTAT	PAYDATE
林佳穎	0	13-JUL-22
林佳穎	1	14-JUL-22
翁翠霙	0	13-JUL-22
劉瑞豐	0	13-JUL-22

UNAME	CHKPAY	PAYDATE
林佳穎	已繳	2022-07-14 10:10:01
劉瑞豐	未繳	2022-07-13 11:40:02
翁翠霙	未繳	2022-07-13 14:10:00