SQL Server子查询返回多行值问题排查（表数据组合唯一）

核心问题诊断 #

你遇到的报错是因为子查询返回了多行结果，虽然你认为JobMilestones表中JobPaperID+Milestone是唯一组合，但实际执行时，主查询的行关联逻辑或数据本身导致子查询匹配到多条记录。而添加TOP 1后全为NULL，是因为子查询直接引用主查询的计算列别名MilestoneReal存在解析优先级问题，导致匹配逻辑失效。

具体原因 #

1. 计算列别名引用问题：SQL Server 2012中，子查询无法直接引用主查询的计算列别名（如MilestoneReal），实际执行时可能解析错误，导致匹配条件不符合预期，要么返回多行，要么无匹配返回NULL。
2. 数据唯一性偏差：可能实际数据中JobPaperID+Milestone组合存在重复（比如隐式类型转换、大小写敏感导致的重复匹配，或者未发现的重复记录）。

解决方案 #

方案1：用LEFT JOIN替代子查询（推荐） #

将相关子查询改为LEFT JOIN，既避免子查询返回多行的问题，逻辑也更清晰：

SELECT 
    mjd.ID AS JobID,
    mjd.JobNo AS JobNo, 
    mjd.CreateDateTime AS JobCreatedDateTime, 
    mjd.InvoiceCustomerCode AS CustomerCode, 
    mjd.MilestoneGroup AS MilestoneGroup, 
    mg.Name AS MilestoneGroupDescr, 
    ml.MilestoneID AS MilestoneID, 
    CASE m.CustomType
        WHEN 0 THEN m.Type
        ELSE m.CustomType
    END AS MilestoneReal, 
    m.Name AS MilestoneName,
    jp.ID AS JobPaperID, 
    jp.Name AS JobPaperName,
    jms.CompletedDate AS CompletedDate
FROM 
    dbo.MainJobDetails AS mjd
INNER JOIN 
    dbo.MilestoneGroup AS mg ON mjd.MilestoneGroup = mg.Code
INNER JOIN 
    dbo.JobPaper AS jp ON mjd.ID = jp.JobID
INNER JOIN 
    dbo.MilestoneLink AS ml ON mg.ID = ml.MilestoneGroupID
INNER JOIN 
    dbo.Milestones AS m ON ml.MilestoneID = m.ID
LEFT JOIN 
    dbo.JobMilestones AS jms 
    ON jms.JobPaperID = jp.ID 
    AND jms.Milestone = CASE m.CustomType
                            WHEN 0 THEN m.Type
                            ELSE m.CustomType
                        END
WHERE 
    mjd.ID = 87101 

方案2：修复子查询的匹配逻辑 #

如果坚持使用子查询，不要直接引用主查询的计算列别名，而是将CASE逻辑直接写入子查询的WHERE条件：

SELECT 
    mjd.ID AS JobID,
    mjd.JobNo AS JobNo, 
    mjd.CreateDateTime AS JobCreatedDateTime, 
    mjd.InvoiceCustomerCode AS CustomerCode, 
    mjd.MilestoneGroup AS MilestoneGroup, 
    mg.Name AS MilestoneGroupDescr, 
    ml.MilestoneID AS MilestoneID, 
    CASE m.CustomType
        WHEN 0 THEN m.Type
        ELSE m.CustomType
    END AS MilestoneReal, 
    m.Name AS MilestoneName,
    jp.ID AS JobPaperID, 
    jp.Name AS JobPaperName,
    (SELECT jms.CompletedDate
     FROM dbo.JobMilestones AS jms
     WHERE jms.JobPaperID = jp.ID 
       AND jms.Milestone = CASE m.CustomType
                                WHEN 0 THEN m.Type
                                ELSE m.CustomType
                            END) AS CompletedDate
FROM 
    dbo.MainJobDetails AS mjd
INNER JOIN 
    dbo.MilestoneGroup AS mg ON mjd.MilestoneGroup = mg.Code
INNER JOIN 
    dbo.JobPaper AS jp ON mjd.ID = jp.JobID
INNER JOIN 
    dbo.MilestoneLink AS ml ON mg.ID = ml.MilestoneGroupID
INNER JOIN 
    dbo.Milestones AS m ON ml.MilestoneID = m.ID
WHERE 
    mjd.ID = 87101 

验证数据唯一性 #

执行以下查询，确认JobMilestones表中是否存在重复的JobPaperID+Milestone组合：

SELECT JobPaperID, Milestone, COUNT(*) AS RecordCount
FROM dbo.JobMilestones
GROUP BY JobPaperID, Milestone
HAVING COUNT(*) > 1

如果返回结果，说明存在重复数据，需要清理或添加唯一约束。

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