SQL查询——获取最近的修订

I'm using T-Sql with SQL Server 2008. Say I have parent table:

我使用的是SQL Server 2008的T-Sql。假设我有父表:

Projects:

项目:

ProjectID  ProjectNam
1          Test Project 1
2          Test Project 2

and child table ProjectRevisions:

和子表ProjectRevisions:

ProjectRevID ProjectID DateCreated
11           1         10/15/2009
12           1         10/19/2009
13           1         10/25/2009
21           2         10/05/2009

How do I end up with the most recent ProjectRevision for each Project? Like this:

我如何完成每个项目的最新项目?是这样的:

ProjectRevID ProjectID DateCreated
13           1         10/25/2009
21           2         10/05/2009

3 个解决方案

#1

The query below will work regardless of any relationship between ProjectRevId and DateCreated.

无论ProjectRevId和DateCreated之间的任何关系，下面的查询都将正常工作。

SELECT *
FROM ProjectRevisions
INNER JOIN (
  SELECT ProjectId
    , MAX(DateCreated) AS DateCreated
  FROM  ProjectRevisions
  GROUP BY ProjectId
  ) AS CurrentRevision
  ON CurrentRevision.ProjectId = ProjectRevisions.ProjectId
  AND CurrentRevision.DateCreated = ProjectRevisions.DateCreated

#2

select x.mProjectRevID, p.ProjectID, p.ProjectNam, x.mDateCreated
from Projects p
inner join
(
   select projectID
   , max(ProjectRevID) as mProjectRevID
   , max(DateCreated) as mDateCreated
   from ProjectRevisions
   group by ProjectID
) x
on x.projectID = p.ProjectID

assuming that ProjectRevID and DateCreated are both "going in the same direction" i.e. the next revision receives a higher ID than the previous one.

假设ProjectRevID和DateCreated都是“朝同一个方向前进”，即下一个修订接收到的ID比前一个更高。

Joining from Projects allows you to access other columns from Projects, if need be.

如果需要的话，加入项目可以访问来自项目的其他列。

#3

select ProjectRevID, ProjectID, DateCreated
from Projects p
  inner join ProjectRevisions
  on ProjectRevisions.ProjectId = p.ProjectId
where ProjectRevId = (
  select ProjecRevId
  from ProjectRevisions
  where ProjectId = p.ProjectId
  order by DateCreated desc
  limit 1
  )

#1