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

Question

A表为主表(问题表),B表示关联表(答案表),A表中一个问题对应B表中多个回答,如A Ba1,... b1,a1,Time b2,a1,Timeselect * 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.tablea
INNER JOIN temp ON dbo.tablea.a1 = temp.a1