关联多表后SQL查询返回两行，如何合并为单行结果？

问题分析 #

你的SQL语句通过OR条件关联NMaterial表，导致OriginMaterial_FN和MaterialCode_PFN分别匹配到NMaterial中的记录时，生成两行数据。DISTINCT和GROUP BY无法解决是因为这两行的部分字段值存在差异，无法直接合并。

解决方案 #

不要用OR做关联条件，而是两次关联NMaterial表，分别对应原始物料和替代物料，就能在同一行中获取所有所需字段：

SELECT 
    A.ModelCode_PFN AS ModelCode
    , B.Name_N AS ModelName
    , A.OriginMaterial_FN AS OriginMaterial
    , C1.PartNumber_N AS OriginPartNo
    , C1.Property_N AS OriginProperty
    , C1.Vender_N AS OriginVender
    , A.MaterialCode_PFN AS AltMaterial
    , A.PartNumber_N AS AltPartNo
    , C2.Property_N AS AltProperty
    , C2.Vender_N AS AltVender
FROM 
    NBOM AS A WITH(NOLOCK)
INNER JOIN 
    NModel AS B WITH(NOLOCK) ON A.ModelCode_PFN = B.Code_PNU
LEFT JOIN 
    NMaterial AS C1 WITH(NOLOCK) ON A.OriginMaterial_FN = C1.Code_PNU
LEFT JOIN 
    NMaterial AS C2 WITH(NOLOCK) ON A.MaterialCode_PFN = C2.Code_PNU
WHERE 
    A.OriginMaterial_FN = '0RHAA-0211A'

补充说明 #

• 用LEFT JOIN可以避免因某一方物料不存在于NMaterial表时丢失整条记录，如果确认两个物料都一定存在，也可以替换为INNER JOIN。
• 给两次关联的NMaterial表分别取别名C1和C2，明确区分原始物料和替代物料的字段来源，从而将原本分散在两行的数据整合到同一行。

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