多表联查 若某一字段相同 且有状态跟时间字段,选取该状态下 时间最近的一条数据

时间:2021-09-29 15:12:47
select info.* from (


select 
             ws.pk,
             ra.pk                            poaId,
         rb.resource_name,
         rb.resource_type,
         to_char(ra.calendar_start_time,'yyyy-MM-dd hh24:mi:ss')   start_time,
         to_char(ra.calendar_end_time,'yyyy-MM-dd hh24:mi:ss')     end_time,
         bu.user_name,  
         ws.bar_code, 
         ra.WORK_FLOW_STATUS,  
         bd.department_name
    
         from rm_warehousing_stock               ws  
        left join rm_base                rb
          on rb.pk = ws.rm_base_pk
        left join rm_resource_apply                     ra
          on ws.pk = ra.warehousing_stock_pk
        left join base_user               bu
          on ra.PARTICIPANT = bu.user_code
        left join base_department            bd
          on bd.department_code = bu.department_code
          where resource_type='2' and ws.publish_status='1'
            and not exists(select 1 from rm_resource_apply sub where sub.work_flow_status !='6' and ra.warehousing_stock_pk=sub.warehousing_stock_pk 
                 and ra.calendar_start_time > sub.calendar_start_time   ) ) info where info.work_flow_status !='6' or info.work_flow_status is null