sql - 如何使用 group by 子句从每个用户的两个子查询的结果集中获取 max( latest_modified_time) 记录？

Question

我有两个获取记录列表的查询。我需要过滤这两个结果集，以便我的最终结果集包含每个用户的 latest_modified_time 列的最大值。怎么做 ？

查询 1:

select us.user_id as user_id , us.modified_datetime as  latest_modified_time , CONCAT(ui.first_name,' ',ui.last_name) as full_name
from lsa_user_skill us 
left join lsa_user_info ui on  us.modified_user_id=  ui.user_id 
where (us.user_id, us.modified_datetime ) IN 
             (select  us.user_id, MAX(us.modified_datetime) from lsa_user_skill us group by  us.user_id) ;

查询 2:

select  sal.affected_user_id  as user_id, sal.modified_timestamp as latest_modified_time , CONCAT(info1.first_name,' ', info1.last_name) as full_name 
 from lsa_skill_auditlog sal
 left join lsa_user_info info on info.user_id=sal.affected_user_id 
 left join lsa_user_info info1 on info1.user_id=sal.user_id 
 where sal.activity = 'User skill deleted';

如下所述，我尝试使用 UNION 函数，它给出了这两个查询的结果集的总和。但是，不知道如何按 user_id 分组以获取包含每个用户的 max(lates_modified_time) 的记录？

查询尝试是:

select us.user_id as user_id , us.modified_datetime as  latest_modified_time , CONCAT(ui.first_name,' ',ui.last_name) as full from lsa_user_skill us 
             left join lsa_user_info ui on  us.modified_user_id=  ui.user_id 
         where (us.user_id, us.modified_datetime ) IN 
             (select  us.user_id, MAX(us.modified_datetime) from lsa_user_skill us group by  us.user_id)
UNION
select  sal.affected_user_id  as user_id, sal.modified_timestamp as latest_modified_time , CONCAT(info1.first_name,' ', info1.last_name) as full from lsa_skill_auditlog sal
 left join lsa_user_info info on info.user_id=sal.affected_user_id 
 left join lsa_user_info info1 on info1.user_id=sal.user_id 
 where sal.activity = 'User skill deleted'

Answer 1

您可以使用union 和distinct on:

select distinct on (user_id), x.*
from ((select us.user_id as user_id , us.modified_datetime as  latest_modified_time , CONCAT(ui.first_name, ' ', ui.last_name) as full_name
       from lsa_user_skill us left join
            lsa_user_info ui
            on us.modified_user_id = ui.user_id 
      ) union all
      (select sal.affected_user_id  as user_id, sal.modified_timestamp as latest_modified_time, CONCAT(info1.first_name, ' ', info1.last_name) as full_name 
       from lsa_skill_auditlog sal left join
            lsa_user_info info
            on info.user_id = sal.affected_user_id left join
            lsa_user_info info1
            on info1.user_id = sal.user_id 
       where sal.activity = 'User skill deleted'
      )
     ) x
order by user_id, latest_modified_time desc;