如何在mssql存储过程中的#table列中求和 [英] how to sum in #table column in mssql store procedure

问题描述

嗨



我不知道如何在临时表中总结其中一列（TOTAL）

Hi

I do not know how to sum one of the column (TOTAL) in temp table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_Customer_Due_Until_Now]
 @Code nvarchar(30)
AS
BEGIN

SET NOCOUNT ON;
select code as Code, Name as Name, sum(Nett) SINETT into #tableSI  From SIHead Where Code=@code Group By Code,Name
select code as Code, Name as Name, sum(-Nett) SINETT into #tableSR  From SRHead Where Code=@code Group By Code,Name
select code as Code, Name as Name, sum(-ChqAmount) SINETT into #tableRC  From RCHead Where Code=@code Group By Code,Name
select code as Code, Name as Name, sum(Nett) SINETT  into #tableDN  From DNHead  Where Code=@code Group By Code,Name
select code as Code, Name as Name, sum(-Nett) SINETT  into #tableCN  From CNHead Where Code=@code Group By Code,Name

select code,name,SUM(SINETT) AS TOTAL from #tableSI GROUP BY CODE,NAME
UNION
select code,name,SUM(SINETT) AS TOTAL from #tableSR GROUP BY CODE,NAME
UNION
select code,name,SUM(SINETT) AS TOTAL from #tableRC GROUP BY CODE,NAME
UNION
select code,name,SUM(SINETT) AS TOTAL from #tableDN GROUP BY CODE,NAME
UNION
select code,name,SUM(SINETT) AS TOTAL from #tableCN GROUP BY CODE,NAME
drop table #tableSI
drop table #tableSr
drop table #tablerc
drop table #tableDN
drop table #tableCN
END

当前结果是

Current Result is

CODE    NAME                     TOTAL    

DT001 	DAKNA TRADING SDN BHD	-96545.61
DT001 	DAKNA TRADING SDN BHD	-1590
DT001 	DAKNA TRADING SDN BHD	-689
DT001 	DAKNA TRADING SDN BHD	1060
DT001 	DAKNA TRADING SDN BHD	94263.21

请告诉我如何在单身总和行



请帮帮我。



Maideen

pls advice me how to sum total in single line

Pls help me.

Maideen

推荐答案

试试这个...

Try this ...

select t1.code,t1.name,(SUM(t1.SINETT)+SUM(t2.SINETT)+SUM(t3.SINETT)+SUM(t4.SINETT)SUM(t5.SINETT)) AS TOTAL from #tableSI t1 
inner join #tableSR t2 on t1.code=t2.code
inner join #tableRC t3 on t1.code=t3.code
inner join #tableDN t4 on t1.code=t4.code
inner join #tableCN  t5 on t1.code=t5.code

您可以将结果包装在CTE中，然后用它来计算总数像这样：

You can wrap your results in a CTE and then use it to sum the totals like this:

with results as
(
  select code,name,SUM(SINETT) AS TOTAL from #tableSI GROUP BY CODE,NAME
  UNION
  select code,name,SUM(SINETT) AS TOTAL from #tableSR GROUP BY CODE,NAME
  UNION
  select code,name,SUM(SINETT) AS TOTAL from #tableRC GROUP BY CODE,NAME
  UNION
  select code,name,SUM(SINETT) AS TOTAL from #tableDN GROUP BY CODE,NAME
  UNION
  select code,name,SUM(SINETT) AS TOTAL from #tableCN GROUP BY CODE,NAME
)
select * from results
union all
select '' as code, 'grand total' as name, sum(total) as total from results

这将给你这个结果：

This will give you this result:

code  name                  total
----- --------------------- ---------------------------------------
DT001 DAKNA TRADING SDN BHD -96545.61
DT001 DAKNA TRADING SDN BHD -1590.00
DT001 DAKNA TRADING SDN BHD -689.00
DT001 DAKNA TRADING SDN BHD 1060.00
DT001 DAKNA TRADING SDN BHD 94263.21
      grand total           -3501.40

这篇关于如何在mssql存储过程中的#table列中求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！