仅加入“最新"用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
相关文章