两张表一对多的连接,取多记录表中最新的一条数据

Question

A表为主表(问题表),B表示关联表(答案表),A表中一个问题对应B表中多个回答,如
A                      B
a1,...                 b1,a1,Time
                         b2,a1,Time
select * from A left join B on A.a1=B.a1
查出来肯定是两个记录,怎么编写sql查询A,关联上B表中最新的一条记录.

Answer 1

left join

Answer 2

select * from A inner join (select table1.* from b table1 inner jioin b table2 on table1.a1 = table2.a1 and table1.Time>table2.Time) tableB inner join on A.a1=tableB.a1

Answer 3

select * from A as a left join (select * from B where B.Time in (select MAX(B.Time) as Time from B group by B.a1)) as b on a.a1=b.a1

Answer 4

先连接按照时间进行排序，每次都选择第一条（top(1)）!

Answer 5

分组在连接查询。

Answer 6

SELECT A.*, B2.*　FROM A CROSS APPLY ( SELECT TOP 1 B.* FROM B WHERE B.a1 = A.a1 Order by B.Time Desc ) B2

Answer 7

可以使用with as WITH temp AS (SELECT MAX(UpdateTIME) AS updatetime,a1 FROM  dbo.tableb  GROUP BY Question)SELECT * FROM dbo.tableaINNER JOIN temp ON dbo.tablea.a1 = temp.a1