使用多左外连接计算列：获取客户年度COA值并计算同比

现有两张表：CustomerStatementSetup记录客户填报财务数据的年度信息，CustomerStatement存储对应年度与COA代码的数值。需求是针对指定CustomerCode和COACode，将当前年度及上一年度的COAValue放在同一行，以此计算同比公式：((当前年度值-上年度值)/上年度值)*100（例如CustomerCode=42151、COACode=10700时，计算((15-50)/50)*100）。当前编写的SQL无法得到预期结果（预期同一行显示客户编码、COA代码、当前值、上年度值），以下是修正方案。

表结构与测试数据 #

CustomerStatementSetup表 #

CSSCode  CustomerCode   FinancialYear   CreatedOn
-------------------------------------------------
80349     42151          2019          Date
80350     42151          2020          //
71131     42120          2018          //
71132     42120          2017          //

CustomerStatement表 #

CSTCode   CSSCODE  COACode  COAValue       CustomerCode
-------------------------------------------------------
1      80349   10700          50            42151
2      80349   10701          20            42151
3      80350   10700          15            42151
4      80350   10701          45            42151
5      71131   10700          20            42120
6      71131   10701          25            42120
7      71132   10700          150           42120
8      71132   10701          200           42120

现有问题查询代码 #

-- perform calculation against current and previous
select csst.CSSCode,csst.FinancialYear,S.COACode,S.COAValue 
from CustomerStatementSetup csst
left join CustomerStatement S on S.CSSCode =  csst.CSSCode 

-- get coavalue against customercode and coacode
left outer join (
    select  top 1 cst.COAValue,css.CSSCode from  CustomerStatementSetup css
    left join CustomerStatement cst on cst.CSSCode =css.CSSCode 
    where cst.CustomerCode=42151  cst.COACode=10700 ORDER  BY css.financialyear DESC
) C1 
on C1.CSSCode =csst.CSSCode 

-- get previous value against customercode and coacode
left outer join (
    select * from (
        select row_number() OVER (ORDER BY css.FinancialYear desc) as rowNo, cst.COAValue,css.CSSCode
        from  CustomerStatementSetup css
        inner join CustomerStatement cst on cst.CSSCode =css.CSSCode 
        where cst.CustomerCode = 42151  and cst.COACode=10700 
    ) as tbl
    where tbl.rowNo = 2
) P1
on P1.CSSCode =csst.CSSCode

预期查询结果 #

CustomerCode   COACode   CurrentYearValue   PreviousYearValue
--------------------------------------------------------------
42151           10700       15                 50

问题分析与修正方案 #

原查询的核心问题是：从CustomerStatementSetup表出发关联数据，但子查询的关联逻辑无法将两个年度的数值合并到同一行；同时查询结果没有输出需要的CurrentYearValue和PreviousYearValue字段，反而保留了无关的CSSCode等信息。

方案1：使用窗口函数LAG（推荐） #

利用LAG窗口函数直接获取同一客户、同一科目上一年度的数值，逻辑简洁清晰：

WITH YearlyCOA AS (
    SELECT 
        cs.CustomerCode,
        cs.COACode,
        css.FinancialYear,
        cs.COAValue,
        -- 按客户、科目分组，年度排序，获取上一年度的数值
        LAG(cs.COAValue) OVER (PARTITION BY cs.CustomerCode, cs.COACode ORDER BY css.FinancialYear) AS PreviousYearValue
    FROM CustomerStatement cs
    JOIN CustomerStatementSetup css ON cs.CSSCode = css.CSSCode
    WHERE cs.CustomerCode = 42151 AND cs.COACode = 10700
)
SELECT 
    CustomerCode,
    COACode,
    COAValue AS CurrentYearValue,
    PreviousYearValue,
    -- 计算同比，处理除数为0/空的情况
    CASE WHEN PreviousYearValue IS NOT NULL AND PreviousYearValue != 0 
         THEN ROUND(((COAValue - PreviousYearValue) * 100.0 / PreviousYearValue), 2) 
         ELSE NULL 
    END AS YoYPercentage
FROM YearlyCOA
-- 仅取最新年度的记录（确保PreviousYearValue不为空）
WHERE PreviousYearValue IS NOT NULL
ORDER BY FinancialYear DESC
LIMIT 1; -- 不同数据库语法略有差异：SQL Server用TOP 1，Oracle用FETCH FIRST 1 ROWS ONLY

方案2：使用条件聚合（适合固定取最近两年） #

通过条件聚合直接提取当前年度和上一年度的数值：

SELECT 
    cs.CustomerCode,
    cs.COACode,
    -- 获取客户的最新年度数值
    MAX(CASE WHEN css.FinancialYear = (SELECT MAX(FinancialYear) FROM CustomerStatementSetup WHERE CustomerCode = cs.CustomerCode) THEN cs.COAValue END) AS CurrentYearValue,
    -- 获取客户的上一年度数值
    MAX(CASE WHEN css.FinancialYear = (SELECT MAX(FinancialYear)-1 FROM CustomerStatementSetup WHERE CustomerCode = cs.CustomerCode) THEN cs.COAValue END) AS PreviousYearValue,
    -- 计算同比比例
    CASE 
        WHEN MAX(CASE WHEN css.FinancialYear = (SELECT MAX(FinancialYear)-1 FROM CustomerStatementSetup WHERE CustomerCode = cs.CustomerCode) THEN cs.COAValue END) IS NOT NULL 
        AND MAX(CASE WHEN css.FinancialYear = (SELECT MAX(FinancialYear)-1 FROM CustomerStatementSetup WHERE CustomerCode = cs.CustomerCode) THEN cs.COAValue END) != 0
        THEN ROUND(
            (MAX(CASE WHEN css.FinancialYear = (SELECT MAX(FinancialYear) FROM CustomerStatementSetup WHERE CustomerCode = cs.CustomerCode) THEN cs.COAValue END) 
            - MAX(CASE WHEN css.FinancialYear = (SELECT MAX(FinancialYear)-1 FROM CustomerStatementSetup WHERE CustomerCode = cs.CustomerCode) THEN cs.COAValue END)) 
            * 100.0 / MAX(CASE WHEN css.FinancialYear = (SELECT MAX(FinancialYear)-1 FROM CustomerStatementSetup WHERE CustomerCode = cs.CustomerCode) THEN cs.COAValue END),
            2
        )
        ELSE NULL
    END AS YoYPercentage
FROM CustomerStatement cs
JOIN CustomerStatementSetup css ON cs.CSSCode = css.CSSCode
WHERE cs.CustomerCode = 42151 AND cs.COACode = 10700
GROUP BY cs.CustomerCode, cs.COACode;

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