时间戳

首页 > 解决方案 > SQL Sum multiple rows by ID //供SP使用

sql-server - SQL Sum multiple rows by ID //供SP使用

问题描述

我又回来了:)

我确实查看了是否还有其他问题，例如我要问的问题，但是他们正在使用来自不同表的联接，因为我将数据放在一个表中并且不需要任何联接，所以当它时我并不是那么好来到 SQL，所以这对你来说可能很简单，对我来说相当复杂，所以我们开始吧

我有 1 个#table，它返回一个名为 LineTotal 的值，它是 UnitPrice 和 TranDespQty 的总和，好吧，所以每个 InvoiceNo 都有多个 TranLineNo，每个项目都有一个，所以我需要的是 InvoiceTotal 作为发票的每个 LineTotal 的总和如果这有意义

    CREATE PROCEDURE sp_PPEInvEnquiry_Invoice_Summary @InvoiceNoFrom int, 
   @InvoiceNoTo int
   as
    if object_id ('tempdb..#tranData2') is not null 
     drop table #tranData2
     select c.Extra2 as InvoiceNo,
       c.TranLineNo,
       cast(null as varchar(255)) as TranDate,
       cast(null as varchar(255)) as AssetName,
       b.ItemCode,
       cast(0 as Decimal(18,2)) as UnitPrice,
       b.TranDespQty,
       cast(0 as Decimal(18,2)) as LineTotal,   
       cast(0 as Decimal(18,2)) as InvoiceTotal

       into #tranData2 
   from TS_TranHeader a, TS_TranDetail b ,
   (select distinct TranDocNo, TranLineNo, TranSlipNo, ItemCode, CoyCode, Extra2 
   from TS_TranSlip where TranSlipScanned = 'P')  c,
   (Select CoyCode,StoreCode,StoreName From TS_Store Where CoyCode = 'KWV') d
   where a.TranDocNo = b.TranDocNo 
   and a.TranDocNo = c.TranDocNo 
   and a.CoyCode = c.CoyCode 
   and a.CoyCode = b.CoyCode 
   and a.CoyCode = 'KWV' 
   and b.TranLineNo = c.TranLineNo
   and a.StoreCode = d.StoreCode

   update a 
   set AssetName = b.AssetName
   from #tranData2 a, TW_Asset b --updates the AssetName and AssetCode 

   update a --updates the trandate
   set a.TranDate = b.TranDate
   from #tranData2 a,TS_TranHeader b
   where a.InvoiceNo -100 = b.TranDocNo

   update a
   set    a.UnitPrice = b.ItemSellingPrice
   from #tranData2 a, TS_Item b, TS_Store d
   where  a.ItemCode = b.ItemCode 

   update a
   set LineTotal = UnitPrice * TranDespQty
   from #tranData2 a

关于我的桌子的外观有一个想法，下面是我正在谈论的列的屏幕截图

https://prnt.sc/qs7r1f

红色是所有相同的发票编号蓝色是需要求和并添加到黑色的行总计是发票编号的 InvoiceTotal（在这种情况下）137

标签： sql-servertsqlstored-procedures

解决方案

在搜索其他论坛并改写我的问题大约一个小时后，我想我得到了答案

select InvoiceNo,InvoiceDate, AssetName,sum(LineTotal)as TotalInvoiceValue,WeekNumber
from #tranData2
where InvoiceNo >= @InvoiceNoFrom
and InvoiceNo <= @InvoiceNoTo 
group by InvoiceNo, InvoiceDate, AssetName,WeekNumber

推荐阅读