多表关联分组计算求助：4张表关联并按指定字段完成统计计算

需求概述 #

需关联4张数据表，将其中3张表的数据计算后整合到Customer表的查询结果中，最终按customerId和baseCurrencyId字段分组输出。

涉及视图与数据表 #

• ViewCustomerDebt：包含customerId、baseCurrencyId、totalOldDebt、totalRecoveredDebt、totalPaidDebt字段
• ViewCustomerSell：包含customerId、paymentTypeId、baseCurrencyId、totalPrice等字段，paymentTypeId取值为1或2
• ViewCustomerPurchase：包含customerId、paymentTypeId、baseCurrencyId、totalPrice等字段
• Customer表：作为输出统计结果的主表

统计输出要求 #

按customerId和baseCurrencyId分组，输出以下字段：

• customerId、baseCurrencyId
• totalPaidSellPrice：ViewCustomerSell中paymentTypeId=1的totalPrice总和
• totalDebtSellPrice：ViewCustomerSell中paymentTypeId=2的totalPrice总和
• totalSellDebt：totalDebtSellPrice + ViewCustomerDebt.totalOldDebt - ViewCustomerDebt.totalRecoveredDebt
• totalPaidPurchasePrice：ViewCustomerPurchase中paymentTypeId=1的totalPrice总和
• totalDebtPurchasePrice：ViewCustomerPurchase中paymentTypeId=2的totalPrice总和
• totalPurchaseDebt：totalDebtPurchasePrice - ViewCustomerDebt.totalPaidDebt
• totalDebtExchange：totalSellDebt - totalPurchaseDebt

当前问题 #

已编写SQL语句但无法得到预期结果，当前代码如下：

SELECT C.id as customerId,
    COALESCE (VCD.baseCurrencyId, VCSP.baseCurrencyId, VCSD.baseCurrencyId, VCPP.baseCurrencyId, VCPD.baseCurrencyId) 
        AS baseCurrencyId,
    VCSP.totalPrice as totalPaidSellPrice,
    VCSD.totalPrice as totalDebtSellPrice,
    VCSD.totalPrice + VCD.totalOldDebt - VCD.totalRecoveredDebt as totalSellDebt,
    VCPP.totalPrice as totalPaidPurchasePrice,
    VCPD.totalPrice as totalDebtPurchasePrice,
    VCPD.totalPrice - VCD.totalPaidDebt as totalPurchaseDebt,
    (VCSD.totalPrice + VCD.totalOldDebt - VCD.totalRecoveredDebt) -
        (VCPD.totalPrice - VCD.totalPaidDebt) as totalExchange
from ViewCustomerDebt as VCD
Full outer join ViewCustomerSell as VCSP on VCD.customerId = VCSP.customerId and 
    VCD.baseCurrencyId = VCSP.baseCurrencyId and VCSP.paymentTypeId = 1
Full outer join ViewCustomerSell as VCSD on VCD.customerId = VCSD.customerId 
    and VCD.baseCurrencyId = VCSD.baseCurrencyId and VCSD.paymentTypeId = 2
Full outer join ViewCustomerPurchase as VCPP on VCD.customerId = VCPP.customerId and 
    VCD.baseCurrencyId = VCPP.baseCurrencyId and VCPP.paymentTypeId = 1
Full outer join ViewCustomerPurchase as VCPD on VCD.customerId = VCPD.customerId 
    and VCD.baseCurrencyId = VCPD.baseCurrencyId and VCPD.paymentTypeId = 2
INNER JOIN dbo.Customer AS C ON C.id = 
        COALESCE (VCD.customerId, VCSP.customerId, VCSD.customerId, VCPP.customerId, VCPD.customerId)

修正方案 #

原语句多次全外连接会导致数据重复，需先对销售、采购视图做分组聚合，再关联债务视图和客户表，修正后代码如下：

SELECT 
    C.id AS customerId,
    COALESCE(VCD.baseCurrencyId, VS.baseCurrencyId, VP.baseCurrencyId) AS baseCurrencyId,
    COALESCE(VS.totalPaidSellPrice, 0) AS totalPaidSellPrice,
    COALESCE(VS.totalDebtSellPrice, 0) AS totalDebtSellPrice,
    COALESCE(VS.totalDebtSellPrice, 0) + COALESCE(VCD.totalOldDebt, 0) - COALESCE(VCD.totalRecoveredDebt, 0) AS totalSellDebt,
    COALESCE(VP.totalPaidPurchasePrice, 0) AS totalPaidPurchasePrice,
    COALESCE(VP.totalDebtPurchasePrice, 0) AS totalDebtPurchasePrice,
    COALESCE(VP.totalDebtPurchasePrice, 0) - COALESCE(VCD.totalPaidDebt, 0) AS totalPurchaseDebt,
    (COALESCE(VS.totalDebtSellPrice, 0) + COALESCE(VCD.totalOldDebt, 0) - COALESCE(VCD.totalRecoveredDebt, 0)) -
    (COALESCE(VP.totalDebtPurchasePrice, 0) - COALESCE(VCD.totalPaidDebt, 0)) AS totalDebtExchange
FROM dbo.Customer AS C
LEFT JOIN ViewCustomerDebt AS VCD ON C.id = VCD.customerId
LEFT JOIN (
    SELECT 
        customerId,
        baseCurrencyId,
        SUM(CASE WHEN paymentTypeId = 1 THEN totalPrice ELSE 0 END) AS totalPaidSellPrice,
        SUM(CASE WHEN paymentTypeId = 2 THEN totalPrice ELSE 0 END) AS totalDebtSellPrice
    FROM ViewCustomerSell
    GROUP BY customerId, baseCurrencyId
) AS VS ON C.id = VS.customerId AND COALESCE(VCD.baseCurrencyId, VS.baseCurrencyId) = VS.baseCurrencyId
LEFT JOIN (
    SELECT 
        customerId,
        baseCurrencyId,
        SUM(CASE WHEN paymentTypeId = 1 THEN totalPrice ELSE 0 END) AS totalPaidPurchasePrice,
        SUM(CASE WHEN paymentTypeId = 2 THEN totalPrice ELSE 0 END) AS totalDebtPurchasePrice
    FROM ViewCustomerPurchase
    GROUP BY customerId, baseCurrencyId
) AS VP ON C.id = VP.customerId AND COALESCE(VCD.baseCurrencyId, VS.baseCurrencyId, VP.baseCurrencyId) = VP.baseCurrencyId
GROUP BY C.id, COALESCE(VCD.baseCurrencyId, VS.baseCurrencyId, VP.baseCurrencyId), 
         VS.totalPaidSellPrice, VS.totalDebtSellPrice,
         VCD.totalOldDebt, VCD.totalRecoveredDebt,
         VP.totalPaidPurchasePrice, VP.totalDebtPurchasePrice,
         VCD.totalPaidDebt
HAVING COALESCE(VCD.baseCurrencyId, VS.baseCurrencyId, VP.baseCurrencyId) IS NOT NULL;

修正说明 #

1. 先对ViewCustomerSell和ViewCustomerPurchase按customerId、baseCurrencyId分组，用CASE语句分别统计不同paymentTypeId的金额总和，避免多次连接导致的数据重复
2. 以Customer表为主表做左连接，确保所有客户数据被覆盖（若不需要可调整连接类型）
3. 用COALESCE处理NULL值，避免计算结果出现NULL
4. 最终按customerId和baseCurrencyId分组，符合需求要求

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