SQL Join获取最新记录 [英] SQL Join to get most recent record

问题描述

我有三个表:

• Measurements (MeasureID, Time, Distance, Value)
• Events(EventID, Time Value)
• EventValues (EventDataID, EventID, Type, Value)

• Measurements (MeasureID, Time, Distance, Value)
• Events(EventID, Time Value)
• EventValues (EventDataID, EventID, Type, Value)

我需要为每次测量获取最新事件(过去)及其相关的事件值数据.

I need to get for every measurement, the most recent event (in the past) and its associated eventvalues data.

我当前的查询非常难看:

My current query is quite ugly:

SELECT 
    M.*,
    (SELECT TOP 1 EV.value FROM [Event] E JOIN EventValues EV ON E.EventID = EV.EventID 
     WHERE M.Time >= E.Time ORDER BY M.Time-E.Time) AS Data,
FROM [Measure] M 
ORDER BY M.Distance

并且它只允许我从EventValues表中选择一列(我需要更多)

and it only allows me to select one column from the EventValues table (I need more)

使用联接有什么办法吗?

Is there any way this can be done using a join?

编辑:即使在第一个事件之前，我也需要从测量表中选择所有条目(即，只需为联接选择空数据)

EDIT: I also need to select ALL entries from the measurement table even if they are before the first event (i.e. just select null data for the join)

推荐答案

您可以使用CROSS APPLY.

You can use CROSS APPLY.

SELECT  
    M.*, Data.* 
FROM [Measure] M 
CROSS APPLY
     (SELECT TOP 1 EV.* FROM [Event] E JOIN EventValues EV ON E.EventID = EV.EventID  
     WHERE M.Time >= E.Time ORDER BY E.Time DESC) AS Data
ORDER BY M.Distance 

这篇关于SQL Join获取最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！