SQL Join获取最新记录

问题描述：

我有三个表:

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

相关推荐