sql-server - 加入子查询与加入表 - 为什么加入表不正确？

Question

我有 2 个查询；

• 1 与子查询连接 - 这是正确拉回一切
• 1 与连接 - 这是在 GrossAnnualDebit 中撤回不正确的计算，并且总体上远低于来自子查询连接的 GrossAnnualDebit 数字。

SELECT prty_id AS PropertyID, 
       ISNULL(SUM(tr.grs_val_trans), 0) + ISNULL(SUM(voi.grs_valtrs), 0) AS GrossAnnualDebit
FROM qlfdat..hgmprty1 p1
     LEFT JOIN
(
    SELECT prty_ref, 
           SUM(grs_val_trans) AS grs_val_trans
    FROM qlfdat..hratrans
    WHERE trans_ppyy BETWEEN 201805 AND 201904
          AND trans_type = 'D'
    GROUP BY prty_ref
) AS tr ON tr.prty_ref = p1.prty_id
     LEFT JOIN
(
    SELECT prty_ref, 
           SUM(grs_valtrs) AS grs_valtrs
    FROM qlfdat..hraptvtt
    WHERE trans_ppyy BETWEEN 201805 AND 201904
    GROUP BY prty_ref
) AS voi ON voi.prty_ref = p1.prty_id
GROUP BY prty_id;

SELECT prty_id AS PropertyID, 
       ISNULL(SUM(tr.grs_val_trans), 0) + ISNULL(SUM(voi.grs_valtrs), 0) AS GrossAnnualDebit
FROM qlfdat..hgmprty1 p1
     LEFT JOIN qlfdat..hratrans AS tr ON tr.prty_ref = p1.prty_id
                                         AND tr.trans_type = 'D'
                                         AND tr.trans_ppyy BETWEEN 201805 AND 201904
     LEFT JOIN qlfdat..hraptvtt AS voi ON voi.prty_ref = p1.prty_id
                                          AND voi.trans_ppyy BETWEEN 201805 AND 201904
                                  AND voi.trans_ppyy = tr.trans_ppyy
GROUP BY prty_id;

标签： sql-serverjoinsubqueryleft-join

Answer 1

如果没有示例代码，我可以准确地判断出问题所在，但我可以看到的不同之处在于第二个查询，您的voi 表不再是left join with p1表，您将其与tr表连接起来，这可能会导致您的问题。