根据id去重,取第一条数据
select t.id, t.name, t.time_c
from (
select id,
name,
time_c,
row_number() over(partition by id order by time_c desc) rank
from table_name
) t
where t.rank = 1
参考:https://blog.csdn.net/iKuboo/article/details/103668567
实际使用CASE:
select t.infoid, t.submitdate, ceil(round((unix_timestamp()-(t.submitdate/1000))/360/24/30,2) )as shelfupMonth
from (
select infoid,
submitdate,
row_number() over(partition by infoid order by submitdate desc) rank
from (SELECT * from hdp_lbg_zhaopin_defaultdb.t_zp_zpshelfuprecord where dt >="20230620" and requestIsShelfup ="true" and shelfupCode ="0") a
) t
where t.rank = 1