通过sql在组上暴露更多字段

Question

我知道，在Group By中你不能Select一个不在聚合函数或GROUP BY子句中的字段

但是，必须使用连接或其他方法来解决这个问题。

我有两个表BMP_VISITS_SITES和BMP_VISITS_COMMENTS，它们由StationID以一对多的关系连接。一个站点可以有很多评论。

我试图写一个查询，返回所有网站和最新的(只有1)评论。我有一个"工作"查询，但它只返回两个列，无论是在聚合函数或组。

这是我的"工作"查询:

select          a.StationID, 
                MAX(b.[dateobserved]) as LastDateObserved,
                a.Status
from            BMP_VISITS_SITES a
left outer join BMP_VISITS_COMMENTS as b 
on a.[StationID] = b.[StationID]
group by a.StationID;

但是我如何访问两个表中的所有列呢?

我已经尝试了1/2成功的内连接。当我将BMP_VISITS_SITES连接到上面的查询时，我得到表(t1)的所有字段。很好，但是一旦我尝试加入BMP_VISITS_COMMENTS (t3)，我得到的结果比我应该的更多。

select          t1.*, t2.*
                --,t3.* 
                from BMP_VISITS_SITES t1
inner join (
        select a.StationID, MAX(b.[dateobserved]) as LastDateObserved from BMP_VISITS_SITES a
        left outer join BMP_VISITS_COMMENTS as b 
        on a.[StationID] = b.[StationID]
        group by a.StationID
) t2 on t2.StationID = t1.StationID
--inner join sde.BMP_VISITS_COMMENTS t3 on t3.StationID = t2.StationID;

Answer 1

SELECT a.*, b.* FROM
BMP_VISITS_SITES a
OUTER APPLY
(
    SELECT TOP 1 *
    FROM BMP_VISITS_COMMENTS b
    WHERE b.StationID = a.StationID
    ORDER BY LastDateObserved DESC
) b

您可以使用apply来获取最后一条评论记录，并从查询的两边返回所有字段。

Answer 2

使用row_number()

select * 
from 
(
    select          a.StationID, 
                    a.Status,
                    b.*,
                    row_number() over (partition by a.stationid, a.status order by b.[dateobserved] desc) as rn
    from            BMP_VISITS_SITES a
    left outer join BMP_VISITS_COMMENTS as b 
    on a.[StationID] = b.[StationID]
) v
where rn = 1