Transact SQL：PaymentRequest关联两张子表获取预期查询结果的方法

问题根因 #

直接对PaymentRequests（PR）表同时左连WorkProgresses和AdvanceByWarranties两个子表时，数据库会将同一条PR记录匹配到的两类付款信息合并到同一行，不会拆分为独立的付款记录行，因此最终返回行数少于预期。

正确实现代码 #

使用UNION ALL分别关联两类付款表后合并结果即可得到预期记录：

select 
pr.ProjectId, pr.NumberPaymentState, 
wp.ToCollectAmmount as WPAmmount, wp.ToCollectPercent as WPPercent,
null as AWAmmount, null as AWPercent
from PaymentRequests pr
left join WorkProgresses wp on (wp.ProjectId = pr.ProjectId and wp.NumberPaymentState = pr.NumberPaymentState)

union all

select 
pr.ProjectId, pr.NumberPaymentState, 
null as WPAmmount, null as WPPercent,
aw.ToCollectAmmount as AWAmmount, aw.ToCollectPercent as AWPercent
from PaymentRequests pr
left join AdvanceByWarranties aw on (aw.ProjectId = pr.ProjectId and aw.NumberPaymentState = pr.NumberPaymentState)

逻辑说明 #

• 上半段查询返回进度款（WorkProgress）类付款记录，预付款相关字段统一填充为null
• 下半段查询返回质保预付款（AdvanceByWarranty）类付款记录，进度款相关字段统一填充为null
• UNION ALL会保留两段查询的所有结果行，不会自动去重，最终返回条数为两类付款记录条数之和，符合预期。

内容的提问来源于stack exchange，提问作者Hernan