SQL Server报表查询异常：支付记录重复显示总额而非明细

我正在编写SQL Server查询生成账户财务报表，需求是列出指定期间内的所有单笔支付记录，并在最后一行汇总支付总额。但当前查询结果中，每一行都显示该期间的支付总额，而非对应单笔支付金额。

当前查询语句 #

SELECT tbl1.fstrAccountID,
       tbl1.flngAccountKey,
       tbl1.fstrName,
       CASE tbl1.fblnEstimate WHEN 1 THEN 'Yes' ELSE 'No' END AS fstrEstimate,
       tbl1.fdtmFilingPeriod,
       tbl1.fcurOil,
       tbl1.fcurOilExp,
       tbl1.fcurNetOil,
       tbl1.fcurNaturalGas,
       tbl1.fcurNaturalGasExp,
       tbl1.fcurNetNaturalGas,
       tbl1.fcurCoalbedMethane,
       tbl1.fcurCoalbedMethaneExp,
       tbl1.fcurNetCoalbedMethane,
       tbl1.fcurSandOrGravel,
       tbl1.fcurLimestoneSandstone,
       tbl1.fcurOtherNaturalResource,
       tbl1.fcurNatGasTransportAllowance,
       tbl1.flngNatGasMCFs,
       tbl1.fcurOilTransportAllowance,
       tbl1.flngOilBarrels,
       tbl1.flngMidVolNatGasBarrels,
       tbl1.fcurMidVolNatGasTransportAllowance,
       tbl1.flngMidVolOilMCFs,
       tbl1.fcurMidVolOilTransportAllowance,
       tbl1.fcurOtherTransportAllowance,
       tbl1.fcurTotalTax,
       tbl1.fcurEstCredit + ISNULL(tbl2.fcurTotalCredit, 0) AS fcurCredits,
       tbl1.fcurNetTax,
       tbl1.fcurPNI,
       CASE tbl1.fblnEstimate
           WHEN 0 THEN tbl1.fcurPeriodCredit
           ELSE ISNULL(tbl3.fcurEstPayments, 0)
       END AS fcurPayments,
       ISNULL(tbl3.fdtmAllocated, @pdtmHighDate) AS fdtmAllocationDate,
       (ISNULL(tbl4.fcurPeriodDestJVs, 0) + ISNULL(tbl5.fcurPeriodSourceJVs, 0)) * -1 AS fcurPeriodJVs
FROM (SELECT tblSev.*,
             ISNULL(tblNgd.fcurTransportAllowance, 0) AS fcurNatGasTransportAllowance,
             ISNULL(tblNgd.flngMCFs, 0) AS flngNatGasMCFs,
             ISNULL(tblOd.fcurTransportAllowance, 0) AS fcurOilTransportAllowance,
             ISNULL(tblOd.flngBarrels, 0) AS flngOilBarrels,
             ISNULL(tblMg.flngMcfsBarrels, 0) AS flngMidVolNatGasBarrels,
             ISNULL(tblMg.fcurTransportAllowance, 0) AS fcurMidVolNatGasTransportAllowance,
             ISNULL(tblMo.flngMcfsBarrels, 0) AS flngMidVolOilMCFs,
             ISNULL(tblMo.fcurTransportAllowance, 0) AS fcurMidVolOilTransportAllowance,
             ISNULL(tblSo.fcurTransportAllowance, 0) AS fcurOtherTransportAllowance
      FROM (SELECT rs.fblnEstimate,
                   ai.flngAccountKey,
                   rsd.flngDocKey,
                   ai.fstrFormattedID AS fstrAccountID,
                   ai.fstrListFormatName AS fstrName,
                   r.fdtmFilingPeriod,
                   SUM(rsd.fcurGrsValOil) AS fcurOil,
                   SUM(rsd.fcurExemptionOil) AS fcurOilExp,
                   SUM(rsd.fcurTxblOil) AS fcurNetOil,
                   SUM(rsd.fcurGrsValNatGas) AS fcurNaturalGas,
                   SUM(rsd.fcurExemptionNatGas) AS fcurNaturalGasExp,
                   SUM(rsd.fcurTxblNatGas) AS fcurNetNaturalGas,
                   SUM(rsd.fcurGrsValMethane) AS fcurCoalbedMethane,
                   SUM(rsd.fcurExemptionMethane) AS fcurCoalbedMethaneExp,
                   SUM(rsd.fcurTxblMethane) AS fcurNetCoalbedMethane,
                   SUM(rsd.fcurTxblSand) AS fcurSandOrGravel,
                   SUM(rsd.fcurTxblLime) AS fcurLimestoneSandstone,
                   SUM(rsd.fcurTxblOther) AS fcurOtherNaturalResource,
                   SUM(rsd.fcurTaxDueOil) + SUM(rsd.fcurTaxDueNatGas) + SUM(rsd.fcurTaxDueMethane) + SUM(rsd.fcurTaxDueSand) + SUM(rsd.fcurTaxDueLime) + SUM(rsd.fcurTaxDueOther) AS fcurTotalTax,
                   CASE rs.fblnEstimate WHEN 1 THEN wr.fcurCredit ELSE 0 END AS fcurEstCredit,
                   wr.fcurTaxDue AS fcurNetTax,
                   p.FCURINTEREST + p.fcurPenalty AS fcurPNI,
                   ABS(p.fcurCredit) AS fcurPeriodCredit
            FROM tblWV_ReturnSEVDelta rsd,
                 tblWV_ReturnSEV rs,
                 tblWV_ReturnInfo ri,
                 tblWV_Return wr,
                 tblReturn r,
                 tblAccountInfo ai,
                 tblPeriod p
            WHERE rsd.flngDocKey = ri.flngDocKey
              AND rsd.flngDocKey = rs.flngDocKey
              AND rsd.flngDocKey = wr.flngDocKey
              AND wr.flngVerLast = 0
              AND ri.flngReturnKey = r.flngReturnKey
              AND r.flngVer = 0
              AND r.fdtmFilingPeriod >= @pdtmDateFrom
              AND r.fdtmFilingPeriod <= @pdtmDateTo
              AND r.flngAccountKey = ai.flngAccountKey
              AND ai.flngAccountKey = p.FLNGACCOUNTKEY
              AND r.fdtmFilingPeriod = p.FDTMFILINGPERIOD
              AND p.FLNGVER = 0
              AND p.fblnActive = 1
            GROUP BY rsd.flngDocKey,
                     ai.flngAccountKey,
                     ai.fstrFormattedID,
                     ai.fstrListFormatName,
                     r.fdtmFilingPeriod,
                     rs.fblnEstimate,
                     wr.fcurCredit,
                     wr.fcurTaxDue,
                     p.FCURINTEREST,
                     p.fcurPenalty,
                     p.fcurCredit) tblSev
          LEFT JOIN (SELECT so.flngDocKey,
                            SUM(so.fcurTransportAllowance) AS fcurTransportAllowance
                     FROM tblWV_ReturnSEVOtherDelta so
                     WHERE so.fcurTransportAllowance > 0
                     GROUP BY so.flngDocKey) tblSo ON tblSev.flngDocKey = tblSo.flngDocKey
          LEFT JOIN (SELECT ngd.flngDocKey,
                            SUM(ngd.fcurTransportAllowance) AS fcurTransportAllowance,
                            SUM(ngd.flngMCFs) AS flngMCFs
                     FROM tblWV_ReturnSEVNaturalGasDelta ngd
                     WHERE ( ngd.fcurTransportAllowance > 0
                          OR ngd.flngMCFs > 0)
                     GROUP BY ngd.flngDocKey) tblNgd ON tblSev.flngDocKey = tblNgd.flngDocKey
          LEFT JOIN (SELECT od.flngDocKey,
                            SUM(od.fcurTransportAllowance) AS fcurTransportAllowance,
                            SUM(od.flngBarrels) AS flngBarrels
                     FROM tblWV_ReturnSEVOilDelta od
                     WHERE ( od.fcurTransportAllowance > 0
                          OR od.flngBarrels > 0)
                     GROUP BY od.flngDocKey) tblOd ON tblSev.flngDocKey = tblOd.flngDocKey
          LEFT JOIN (SELECT mg.flngDocKey,
                            SUM(mg.fcurTransportAllowance) AS fcurTransportAllowance,
                            SUM(mg.flngMcfsBarrels) AS flngMcfsBarrels
                     FROM tblWV_ReturnSEVMidVolumeDelta mg
                     WHERE mg.fstrType = 'NATGAS'
                       AND ( mg.fcurTransportAllowance > 0
                          OR mg.flngMcfsBarrels > 0)
                     GROUP BY mg.flngDocKey) tblMg ON tblSev.flngDocKey = tblMg.flngDocKey
          LEFT JOIN (SELECT mo.flngDocKey,
                            SUM(mo.fcurTransportAllowance) AS fcurTransportAllowance,
                            SUM(mo.flngMcfsBarrels) AS flngMcfsBarrels
                     FROM tblWV_ReturnSEVMidVolumeDelta mo
                     WHERE mo.fstrType = 'OIL'
                       AND ( mo.fcurTransportAllowance > 0
                          OR mo.flngMcfsBarrels > 0)
                     GROUP BY mo.flngDocKey) tblMo ON tblSev.flngDocKey = tblMo.flngDocKey) tbl1
    LEFT JOIN (SELECT tc.flngDocKey,
                      tc.fcurTotalCredit
               FROM tblWV_ReturnSEVDelta rsd,
                    tblWV_ReturnSEV rs,
                    tblWV_ReturnScheduleTC tc,
                    tblWV_ReturnInfo ri,
                    tblWV_Return wr,
                    tblReturn r
               WHERE rsd.flngDocKey = ri.flngDocKey
                 AND rsd.flngDocKey = tc.flngDocKey
                 AND tc.flngVerLast = 0
                 AND rsd.flngDocKey = rs.flngDocKey
                 AND rsd.flngDocKey = wr.flngDocKey
                 AND wr.flngVerLast = 0
                 AND ri.flngReturnKey = r.flngReturnKey
                 AND r.flngVer = 0
                 AND r.fblnInError = 0
                 AND r.fdtmFilingPeriod >= @pdtmDateFrom
                 AND r.fdtmFilingPeriod <= @pdtmDateTo
               GROUP BY tc.flngDocKey,
                        tc.fcurTotalCredit) tbl2 ON tbl1.flngDocKey = tbl2.flngDocKey
    LEFT JOIN (SELECT a.FLNGACCOUNTKEY,
                      a.FDTMFILINGPERIOD,
                      CONVERT(date, a.FDTMALLOCATED) AS fdtmAllocated,
                      SUM(a.FCURAMOUNT) AS fcurEstPayments
               FROM tblALLOCATION a
               WHERE a.fdtmFilingPeriod >= @pdtmDateFrom
                 AND a.fdtmFilingPeriod <= @pdtmDateTo
                 AND a.fdtmReversed = @pdtmHighDate
               GROUP BY a.FLNGACCOUNTKEY,
                        a.FDTMFILINGPERIOD,
                        CONVERT(date, a.FDTMALLOCATED)) tbl3 ON tbl1.flngAccountKey = tbl3.FLNGACCOUNTKEY
                                                            AND tbl1.fdtmFilingPeriod = tbl3.FDTMFILINGPERIOD
    LEFT JOIN (SELECT jvp.flngAccountKey,
                      jvp.fdtmFilingPeriod,
                      SUM(jvp.fcurDestAmount) AS fcurPeriodDestJVs
               FROM tblRATxnJVDetailPostedAll jvp
               WHERE jvp.fdtmFilingPeriod >= @pdtmDateFrom
                 AND jvp.fdtmFilingPeriod <= @pdtmDateTo
                 AND jvp.fstrRevenueGroup = 'SEV'
                 AND jvp.fstrDestRAType <> 'REVCRD'
                 AND jvp.fstrDestRAType IN ('SEVOIL', 'SEVGAS', 'SEVMTH')
               GROUP BY jvp.flngAccountKey,
                        jvp.fdtmFilingPeriod) tbl4 ON tbl1.flngAccountKey = tbl4.flngAccountKey
                                                  AND tbl1.fdtmFilingPeriod = tbl4.fdtmFilingPeriod
    LEFT JOIN (SELECT jvp.flngAccountKey,
                      jvp.fdtmFilingPeriod,
                      SUM(jvp.fcurSourceAmount) AS fcurPeriodSourceJVs
               FROM tblRATxnJVDetailPostedAll jvp
               WHERE jvp.fdtmFilingPeriod >= @pdtmDateFrom
                 AND jvp.fdtmFilingPeriod <= @pdtmDateTo
                 AND jvp.fstrRevenueGroup = 'SEV'
                 AND jvp.fstrDestRAType <> 'REVCRD'
                 AND jvp.fstrSourceRAType IN ('SEVOIL', 'SEVGAS', 'SEVMTH')
               GROUP BY jvp.flngAccountKey,
                        jvp.fdtmFilingPeriod) tbl5 ON tbl1.flngAccountKey = tbl5.flngAccountKey
                                                  AND tbl1.fdtmFilingPeriod = tbl5.fdtmFilingPeriod
ORDER BY fstrAccountID,
         fdtmFilingPeriod;

数据示例 #

实际单笔支付数据 #

• 182.84
• 57.59
• 38.31

当前查询结果（支付段） #

期望查询结果（支付段） #

需求目标 #

实现显示3条单笔支付记录，最后一行显示总额。

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