仅加入“最新"用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屋！