仅加入“最新"的用t-sql记录 [英] Join to only the "latest" record with t-sql

问题描述

我有两个桌子.表"B"与表"A"具有一对多关系，这意味着表"A"中的一条记录在表"B"中将有很多记录.

I've got two tables. Table "B" has a one to many relationship with Table "A", which means that there will be many records in table "B" for one record in table "A".

表"B"中的记录主要由日期区分，我需要生成一个结果集，该结果集应包括表"A"中的记录以及仅与表"B"中的最新记录结合在一起的结果集.为了便于说明，下面是一个示例架构:

The records in table "B" are mainly differentiated by a date, I need to produce a resultset that includes the record in table "A" joined with only the latest record in table "B". For illustration purpose, here's a sample schema:

Table A
-------
ID

Table B
-------
ID
TableAID
RowDate

我在为查询提供结果集时遇到麻烦，我将不胜感激.

I'm having trouble formulating the query to give me the resultset I'm looking for any help would be greatly appreciated.

推荐答案

select a.*, bm.MaxRowDate
from (
    select TableAID, max(RowDate) as MaxRowDate
    from TableB
    group by TableAID
) bm
inner join TableA a on bm.TableAID = a.ID

如果您需要TableB中的更多列，请执行以下操作:

If you need more columns from TableB, do this:

select a.*, b.* --use explicit columns rather than * here
from (
    select TableAID, max(RowDate) as MaxRowDate
    from TableB
    group by TableAID
) bm
inner join TableB b on bm.TableAID = b.TableAID
    and bm.MaxRowDate = b.RowDate
inner join TableA a on bm.TableAID = a.ID

这篇关于仅加入“最新"的用t-sql记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！