为分组查询返回没有聚合的列值

Ok, question sound very confusing, I just can't come up with better title.

好吧,问题听起来很混乱,我只是想不出更好的头衔。

Here is my query:

这是我的查询:

SELECT TS.LocationKey, TA.TrailerKey, MAX(TS.ArrivedOnLocal) MaxArrivedOnLocal
    FROM dbo.DSPTripStop TS
        INNER JOIN dbo.DSPTripAssignment TA ON TS.TripStopKey = TA.ToTripStopKey AND TA.TrailerKey IS NOT NULL        
    GROUP BY TS.LocationKey, TA.TrailerKey

Query returns list of trailers with locations and last time they were dropped at that location. This is what I need. MAX(time) for location is a goal.

查询返回具有位置的拖尾列表以及上次在该位置删除的列表。这就是我需要的。位置的MAX(时间)是一个目标。

But I'd like to also know which DSPTripStop.TripStopKey this MAX() time happened on.

但我还想知道这个MAX()时间发生在哪个DSPTripStop.TripStopKey上。

I can't group by this value. I understand that it is not defined (can be multiple values for the same time). For my purpose ANY random will work. But I can't find any better way then joining second time by MaxArrivedOnLocal to get what I need.

我不能按此值分组。我知道它没有定义(同时可以是多个值)。为了我的目的,任何随机都可以。但是我找不到更好的方法然后由MaxArrivedOnLocal第二次加入以获得我需要的东西。

SQL Server already "sees" this data when MAX() aggregated, any way to pull it in this query?

当MAX()聚合时,SQL Server已经“看到”了这个数据,是否可以在此查询中提取它?

2 个解决方案

#1

I think this is what you want. Rather than doing a group by, you partition instead, number the rows, then take the top 1

我想这就是你想要的。不是分组,而是分区,对行进行编号,然后取前1

WITH cte AS
(
    SELECT TS.LocationKey, 
        TA.TrailerKey, 
        TS.ArrivedOnLocal,
        TS.TripStopKey,
        ROW_NUMBER() OVER (PARTITION BY TS.LocationKey, TA.TrailerKey ORDER BY ArrivedOnLocal DESC) rn
    FROM dbo.DSPTripStop TS
    INNER JOIN dbo.DSPTripAssignment TA ON TS.TripStopKey = TA.ToTripStopKey AND TA.TrailerKey IS NOT NULL 
) 
SELECT LocationKey,
    TrailerKey,
    ArrivedOnLocal,
    TripStopKey
FROM cte
WHERE rn = 1

#2

If you need any random value for DSPTripStop.TripStopKey then you can use MAX itself as this will return the latest TripStopKey.

如果您需要DSPTripStop.TripStopKey的任意随机值,那么您可以使用MAX本身,因为这将返回最新的TripStopKey。

    SELECT 
        TS.LocationKey, 
        TA.TrailerKey, 
        MAX(TS.ArrivedOnLocal) MaxArrivedOnLocal,
        MAX(TS.TripStopKey)
    FROM dbo.DSPTripStop TS
    INNER JOIN dbo.DSPTripAssignment TA 
    ON TS.TripStopKey = TA.ToTripStopKey 
    AND TA.TrailerKey IS NOT NULL    
GROUP BY TS.LocationKey, TA.TrailerKey

#1