查询从每个组中查找第二大值

I have three tables:

我有三个表:

project: project_id, project_name
项目:project_id project_name
milestone: milestone_id, milestone_name
里程碑:milestone_id milestone_name
project_milestone: id, project_id, milestone_id, completed_date
project_milestone: id、project_id、milestone_id、completed_date

I want to get the second highest completed_date and milestone_id from project_milestone grouped by project_id. That is I want to get the milestone_id of second highest completed_date for each project. What would be the correct query for this?

我想从project_id分组的project_milestone中获得第二个最高的completed_date和milestone_id。这就是我想要为每个项目获得第二个最高完成日期的里程碑。这个问题的正确查询是什么?

2 个解决方案

#1

I think you can do what you want with the project_milestone table and row_number():

我认为您可以使用project_milestone表和row_number()做您想做的事情:

select pm.*
from (select pm.*,
             row_number() over (partition by project_id order by completed_date desc) as seqnum
      from project_milestone pm
      where pm.completed_date is not null
     ) pm
where seqnum = 2;

If you need to include all projects, even those without two milestones, you can use a left join:

如果您需要包含所有项目，即使没有两个里程碑，您可以使用左连接:

select p.project_id, pm.milestone_id, pm.completed_date
from projects p left join
     (select pm.*,
             row_number() over (partition by project_id order by completed_date desc) as seqnum
      from project_milestone pm
      where pm.completed_date is not null
     ) pm
     on p.project_id = pm.project_id and pm.seqnum = 2;

2 个解决方案

#1

更多相关文章

随机推荐