SQL试算平衡表查询中PIVOT函数语法及多绑定错误解决

问题原因 #

• 语法错误：LEFT JOIN 结束后多写了多余的右括号，导致子查询闭合逻辑异常
• 字段作用域错误：Total_$ 是外层SELECT才定义的计算字段，PIVOT执行优先级高于外层SELECT，无法识别该字段
• 逻辑冲突：PIVOT默认会基于子查询中「除聚合字段、待转列字段之外的所有字段」自动分组，提前写SUM聚合会和PIVOT的分组逻辑冲突

修正后的SQL代码 #

SELECT 
    com,
    fy,
    ledg,
    pl,
    [desc],
    ISNULL([1],0) AS [期间1_金额],
    ISNULL([2],0) AS [期间2_金额],
    ISNULL([3],0) AS [期间3_金额],
    ISNULL([4],0) AS [期间4_金额],
    ISNULL([5],0) AS [期间5_金额],
    ISNULL([6],0) AS [期间6_金额],
    ISNULL([7],0) AS [期间7_金额],
    ISNULL([8],0) AS [期间8_金额],
    ISNULL([9],0) AS [期间9_金额],
    ISNULL([10],0) AS [期间10_金额],
    ISNULL([11],0) AS [期间11_金额],
    ISNULL([12],0) AS [期间12_金额]
FROM (
    SELECT  
        t.com,
        t.fy,
        t.fpr,
        t.ledg,
        b.pl,
        b.[desc],
        ISNULL(t.Debit_$ - t.Credit_$,0) AS Total_$
    FROM (
        SELECT  
            com,
            fy,
            fpr,
            ledg,
            CASE WHEN dc = 1 THEN amt_1 ELSE 0 END AS Debit_$,
            CASE WHEN dc = 2 THEN amt_1 ELSE 0 END AS Credit_$
        FROM dbo.mas31
    ) AS t
    LEFT JOIN dbo.sam13 AS b 
        ON t.ledg = b.ledg AND t.com = b.com
) AS src
PIVOT (
    SUM(Total_$)
    FOR fpr IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS pt

改动说明 #

• 把Total_$的计算下沉到PIVOT的源子查询中，保证PIVOT可以识别该字段
• 删除了多余的右括号，修正语法结构
• 去掉了提前的SUM聚合，交由PIVOT自动按其他维度分组后按期间聚合
• 对desc关键字加了方括号转义，避免关键字冲突
• 转列后对每个期间字段加了ISNULL处理，空值默认显示为0

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