求助：构建含MAX聚合与多表关联的正确SQL查询

表结构说明 #

• Table1KIT_CONT：存储套件唯一ID、套件部件号（KIT_CONT_PN）、套件版本（KIT_CONT_REV）、套件数量（KIT_CONT_QTY）等信息；
• Table2KIT_PN：存储套件唯一ID、套件部件号（KIT_PN）、套件版本（KIT_REV）等信息；
• Table3PN_PRICE：存储部件号（PN）、部件价格（PN_PRICE）、价目表版本（PRICELIST_REV）等信息。

查询需求 #

• 按ID关联Table2KIT_PN与Table1KIT_CONT，仅保留每个套件的最高版本数据；
• 按部件号关联Table1KIT_CONT与Table3PN_PRICE，仅保留每个部件的最高价目表版本数据；
• 返回列：KIT_PN、KIT_CONT_PN、KIT_CONT_QTY、KIT_REV、PN_PRICE、PRICELIST_REV。

遇到的问题 #

自行编写的SQL无法正常运行，且尚未实现筛选最高价目表版本的逻辑，尝试过相关解决方案但未适配成功，恳请技术帮助。

自行编写的查询代码 #

Select
A.KIT_ID
A.KIT_PN,
B.KIT_CONT_PN,
B.KIT_CONT_QTY,
C.PN_PRICE,
C.PN_PRICELIST_REV
FROM Table2KIT_PN   A WITH (NOLOCK) 
left JOIN 
Table1KIT_CONT          B WITH (NOLOCK)
ON A.KIT_ID = B.KIT_CONT_ID
left JOIN 
Table3PN_PRICE          C WITH (NOLOCK)
On B.KIT_CONT_PN = C.PN
and B.KIT_CONT_REV = (Select Max(B.KIT_CONT_REV) FROM Table1KIT_CONT D where B.KIT_CONT_REV = D.KIT_CONT_REV )

内容的提问来源于stack exchange，提问作者Cato.Fuglestad