17,377
社区成员
发帖
与我相关
我的任务
分享
select * from
(select id ,ttime,tvalue,
row_number()over(partition by id, trunc((ttime +8*3600)/(3600*24))--id 天分组
order by abs(mod(ttime +8*3600,3600*24)-23*3600)asc) rn --按照绝对值升序
from tb)
where rn=1
with tb as(select 1 id, 400000 ttime ,'dsads' tvalue from dual union all
select 1 id, 400009 ttime ,'dsads' tvalue from dual union all
select 2,333333 ttime,'dsads' from dual union all
select 2,333334 ttime,'dsads' from dual union all
select 3,733334 ttime,'dsads' from dual union all
select 6,833334 ttime,'dsads' from dual union all
select 6,833333 ttime,'dsads' from dual )
select * from
(select id ,ttime,tvalue,
row_number()over(partition by id, trunc((ttime +8*3600)/(3600*24))--id 天分组
order by abs(mod(ttime +8*3600,3600*24)-23*3600)asc) rn --按照绝对值升序
from tb)
where rn=1