一看到这个需求就想到了行转列,以下就是实现方式
select m.account_no 电子账号,
m.member_usguid 用户ID,
to_char(m.crt_dt, 'yyyymmdd hh24:mi:ss') 注册时间,
login_tm1 最后一次登录时间,
login_tm2 倒数第二次登录时间,
login_tm3 倒数第三次登录时间
from t_member m
left outer join (select member_cd,
max(case
when rn = 1 then
login_tm
end) login_tm1,
max(case
when rn = 2 then
login_tm
end) login_tm2,
max(case
when rn = 3 then
login_tm
end) login_tm3
from (select member_cd,
to_char(g.login_dt, 'yyyy-mm-dd:hh24:mi:ss') login_tm,
to_char(g.login_dt, 'yyyymmdd') login_dt,
row_number() over(partition by g.member_cd order by g.login_dt desc) rn
from t_member_log g)
where rn <= 3
group by member_cd) lg
on (m.member_cd = lg.member_Cd)