SQL Server执行查询报错Msg 512：子查询返回多个值，求正确查询语句

问题原因 #

• 报错本质是SELECT列表中作为列值返回的标量子查询返回了多行数据，SQL规范要求这类子查询必须仅返回1行1列的结果，因此触发512错误。
• 具体触发错误的是Actual字段对应的子查询：该子查询的GROUP BY包含T2.[OcrCode]和T0.[AcctCode]两个字段，当同一个U_Budget_Key对应多个会计科目时，子查询会返回多条记录，不符合标量子查询的要求。
• 额外冗余点：Q8、Open PR、Open PO对应的子查询不需要写GROUP BY语句，WHERE条件已经限定了关联的分组字段等于外层的TB.[U_Budget_Key]，直接调用SUM聚合就只会返回单个结果，冗余的GROUP BY有概率在数据异常时触发同类错误。

修正后的SQL语句 #

SELECT DISTINCT
       TB.[U_Budget_Key] AS [Budget Key],
       (SELECT SUM(T01.[U_Budget_XOF])
        FROM [dbo].[@BUDGET] T01
        WHERE T01.[U_Budget_Key] = TB.[U_Budget_Key]) AS [Q8],
       (SELECT SUM(T1.[OpenSum])
        FROM OPRQ T0
             INNER JOIN PRQ1 T1 ON T0.[DocEntry] = T1.[DocEntry]
             INNER JOIN OOCR T2 ON T1.OcrCode4 = T2.OcrCode
        WHERE T1.[U_Budget] = TB.[U_Budget_Key]
          AND T1.[LineTotal] <> 0
          AND T1.[U_Budget] <> ''
          AND T0.[CANCELED] <> 'Y'
          AND T1.[LineStatus] = 'O') AS [Open PR],
       (SELECT SUM(T1.[OpenSum])
        FROM OPOR T0
             INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
        WHERE T1.[U_Budget] = TB.[U_Budget_Key]
          AND T0.[DocStatus] = 'O'
          AND T1.[LineTotal] <> 0
          AND T1.[U_Budget] <> ' ') AS [Open PO],
       -- 在外层加SUM聚合，把同一预算键下所有科目的借贷差额汇总为单个值
       (SELECT SUM(ISNULL((SELECT SUM(TA.[Debit])
                        FROM JDT1 TA
                        WHERE TA.[Account] = T0.[AcctCode]
                          AND TA.ocrcode4 = T2.[OcrCode]),
                       0) - ISNULL((SELECT SUM(TB_JDT.[Credit])
                                    FROM JDT1 TB_JDT
                                    WHERE TB_JDT.[Account] = T0.[AcctCode]
                                      AND TB_JDT.ocrcode4 = T2.[OcrCode]),
                                   0))
        FROM OBGT T0
             INNER JOIN BGT1 T1 ON T0.[AbsId] = T1.[BudgId]
             INNER JOIN BGT2 T2 ON T0.[AbsId] = T2.[BudgId]
        WHERE T2.[OcrCode] = TB.[U_Budget_Key]
          AND T2.[Instance] = 2) AS [Actual]
FROM [@BUDGET] TB;

说明：修正时同时把子查询里的别名TB改成了TB_JDT，避免和外层主表的别名TB重名冲突，防止出现逻辑错误。

内容的提问来源于stack exchange，提问作者Yann Cedric Tia