这样的SQL语句是不是最合适的写法?

[happydaily]

表A,主表 结构 id,filesname,jiedian
表B,副表 结构 id,bianhao,bzyj,shyj,qfyj,version
表A.id=表B.bianhao,一对多关系,现在要选出表A数据和表B中version最大的数据(实际结果是各一行)
自己写了SQL如下,感觉好复杂啊,不知道有没有写得更好点?

select a.filesname,a.fname,a.jiedian,b.bzyj,b.shyj,b.qfyj from tb_A as a left join tb_B as b on a.id=b.bianhao where a.id=@id and b.id=(select id from tb_B where bianhao=@id and version=(select max(version) from tb_B where bianhao=@id))

请高手指点下,谢谢

[xuam]

用with as

[百年树人]

select a.filesname,a.fname,a.jiedian,b.bzyj,b.shyj,b.qfyj 
from tb_A as a 
left join tb_B as b on a.id=b.bianhao 
and not exists(select 1 from tb_b where bianhao=b.bianhao and version>b.version)
where a.id=@id