sql - 根据另一个表的总和对表的一列求和

Question

我有这个代码:

declare @ReportLines table 
    (RebateInvoiceID int, 
     RebateSetupID int ,
     ShortItemNo float primary key(RebateInvoiceID,RebateSetupID,ShortItemNo),
     TotalAmount float,
     InvoiceTotal float,
     TotalQuantity int )
insert @ReportLines
select
  i.RebateInvoiceID
, coalesce(rs.WholesalerRebateSetupID,r.RebateSetupID)
, bl.ShortItemNo
, sum(round(r.Amount,2)) as TotalAmount
, sum(round(TotalAmount,2)) as InvoiceTotal
, sum(r.Quantity) TotalQuantity
from
  @Invoices i
  join RebateInvoices ri (nolock) on 
    ri.RebateInvoiceID=i.RebateInvoiceID
  inner loop join Rebates r (nolock) on
    r.RebateInvoiceID=i.RebateInvoiceID       
  join RebateSetup rs (nolock) on
    rs.RebateSetupID=r.RebateSetupID
  join BidLines bl (nolock) on 
    r.BidLineGuid=bl.BidLineGuid
  join @Products p on
    p.ShortItemNo=bl.ShortItemNo
  left join ChargebackDetailHistory cd (nolock) on 
    r.DocumentBranchPlant = cd.DocumentBranchPlant
    and r.DocumentNumber = cd.DocumentNumber
    and r.DocumentType = cd.DocumentType
    and r.LineNumber = cd.LineNumber
  left join EDI.dbo.JDE_SaleDetail sd (nolock) on 
    r.DocumentBranchPlant = sd.BranchPlant
    and r.DocumentNumber = sd.OrderNumber
    and r.DocumentType = sd.OrderType
    and r.LineNumber = sd.LineNumber

where 
    cd.InvoiceDate between @BeginDate and @EndDate
    or sd.InvoiceDate between @BeginDate and @EndDate
group by
  i.RebateInvoiceID
, coalesce(rs.WholesalerRebateSetupID,r.RebateSetupID)
, bl.ShortItemNo

我想从总金额列中对发票总金额列求和。当我运行上面的查询时，我得到了一堆奇怪的总数。发票总额应该是每行一个数字，并且与 rebateinvoiceid 一致，因为这是它的分组依据，对吗？

Answer 1

您可以进行另一个 Nesty 查询...像这样:

insert @ReportLines
select
   RebateInvoiceID
   , ID
   , ShortItemNo
   , TotalAmount
   , sum(round(TotalAmount,2)) as InvoiceTotal
   , TotalQuantity
from
( 
   select
   i.RebateInvoiceID
   , coalesce(rs.WholesalerRebateSetupID,r.RebateSetupID) as ID
   , bl.ShortItemNo
   , sum(round(r.Amount,2)) as TotalAmount
   , sum(r.Quantity) TotalQuantity
  from (... <the rest of your code here> ...) 
) As MyTable
group by
 RebateInvoiceID, ID, ShortItemNo, TotalAmount, TotalQuantity