两个表JOIN连接

[新新小白白又白]

表1：

表2:


SELECT a.num,b.text
FROM 表1 a
LEFT JOIN 表2 b ON a.id = b.id

我只需要取表2的Version为最大值的数据与表1相连接，需要怎么修改？

SELECT a.num,bb.text
FROM t1 a
outer apply (select top 1 * from t2 b where a.id = b.id order by [version] desc ) as bb

使用outer apply可以解决，请问可以使用left join解决吗？

[sdk25]

表2，在查询中是作为主表查询的吗？

[sdk25]

如果是以表2为主表作为查询，这样试试 SELECT a.num,b.text FROM 表2 a LEFT JOIN 表1 b ON a.id = b.id WHERE a.Version = (select max(Version) from 表2)

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([id] int,[num] int)
Insert #T1
select 1,125125 union all
select 2,12512535 union all
select 3,12616 union all
select 4,125
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([id] int,[version] int,[text] int)
Insert #T2
select 1,0,215 union all
select 1,1,125 union all
select 2,0,125126 union all
select 3,0,1255
Go
--测试数据结束
SELECT a.num,b.text 
FROM #T1 a
LEFT JOIN (SELECT *,ROW_NUMBER()OVER(PARTITION BY id ORDER BY version DESC) rn FROM #T2) b ON a.id = b.id AND rn=1