排除与Postgres中每个父记录关联的第一条记录

作者: 满目山河
发布时间: 2024-04-18 02:11:57 (1天前)
转自：

3 条回复

0#
回复此人
不见你 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 使用 <code> row_number() </code> 窗口功能 </p> <pre> <code> with cte as ( select e.*, row_number()over(partition by user_id order by start_date desc) rn, count(*) over(partition by user_id) cnt from users u join job_experiences e on u.id=e.user_id ) , cte2 as ( select * from cte ) select * from cte2 t1 where rn<=(select max(cnt)-1 from cte2 t2 where t1.user_id=t2.user_id) </code> </pre> </DIV>

编辑
1#
回复此人
一生浮华 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 您可以使用中间CTE获得第一个（ <code> MIN </code> ）每个用户的作业，然后使用它来确定要排除的记录： </p> <pre> <code> WITH user_first_je("user_id", "job_id") AS ( SELECT "user_id", MIN("id") FROM job_experiences GROUP BY "user_id" ) SELECT job_experiences.* FROM job_experiences LEFT JOIN user_first_je ON user_first_je.job_id = job_experiences.id WHERE user_first_je.job_id IS NULL; </code> </pre> </DIV>

编辑

登录后才能参与评论