如何仅选择表格中的最新条目? [英] How do I select only the latest entry in a table?

问题描述

我有一个 3 表的 SQLServer 数据库.

I have a 3 table SQLServer Database.

Project
ProjectID
ProjectName

Thing
ThingID
ThingName

ProjectThingLink
ProjectID
ThingID
CreatedDate

当一个事物被归于一个项目时，一个条目会被放入 ProjectThingLink 表中.事情可以在项目之间移动.CreatedDate 也用于知道上次移动的是哪个项目.

When a Thing is ascribed to a Project an entry is put in the ProjectThingLink table. Things can move between Projects. The CreatedDate is used to know which Project a Thing was last moved too.

我正在尝试创建一个列表，列出当前与事物相关联的所有项目，但我的大脑出现故障.

I am trying to create a list of all Projects with which Things are currently linked to them, but my brain is failing.

有没有简单的方法可以做到这一点?

Is there an easy way of doing this?

推荐答案

select p.projectName, t.ThingName
from projects p
join projectThingLink l on l.projectId = p.projectId
join thing t on t.thingId = l.thingId
where l.createdDate =
( select max(l2.createdDate)
  from projectThingLink l2
  where l2.thingId = l.thingId
);

注意:评论后更正

这篇关于如何仅选择表格中的最新条目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！