如何在存储过程中计算此表？ [英] How to Calculate this table in stored procedure ?

问题描述

 TABLE tbl1 
 
 ItemId名称数量
 1西红柿2 
 2黄瓜5 
 3橘子3 
 3橙子1 
 
 
表tbl2 
 
 ItemId名称数量
 1西红柿3 
 2黄瓜5 
 2黄瓜10 
 3橘子2 
 
表tbl3 
 
 ItemId名称数量
 1西红柿1 
 1西红柿2 
 2黄瓜5 
 3橙子2 
 
 并希望输出（tbl1 + tbl2-tbl3）作为存储过程中的新临时表
 
 ItemId名称TQuantity 
 1西红柿2 
 2黄瓜15 
 3橘子4  

解决方案

这看起来像家庭作业所以我不会给你一个完整的解决方案。此外，我假设你在SQL服务器上。



1）创建存储过程 [ ^ ]，简单。



2）使用 UNION ALL [ ^ ]连接相同结构的两个输出。

提示：你如何减去tbl3中的qauntities？



3）使用 SUM [ ^ ]汇总函数以添加数量。

提示：你需要放入什么< a href =http://msdn.microsoft.com/en-us/library/ms177673.aspx> GROUP BY [ ^ ]条款？



4）使用 SELECT INTO [ ^ ]。临时表名称以井号（＃）开头。

提示：如何在存储过程外显示表格？



干杯

  set   ANSI_NULLS   ON  
  set   QUOTED_IDENTIFIER  < span class =code-keyword> ON  
  go  
 
  ALTER  过程 [dbo]。[sp_rpt_StockSummary] 
 
  AS  
 开始 
 
  SELECT  P.ID，P.ProductName ，P.ProductCode， COALESCE （cash.CashQuantity， 0 ）+  COALESCE （credit.CreditQuantity， 0 ） -   COALESCE （Oreturn.ReturnQuantity， 0 ） as  TQty 
 
 来自 dbo.Inv_ProductName P 
 
 
  LEFT   JOIN （选择 SUM（数量） as  CashQuantity，ProductID 来自 Inv_PurchaseCashDetails  GROUP   BY  ProductID）cash  ON  P.ID = cash.ProductID 
  LEFT   JOIN （选择 SUM（数量） as  CreditQuantity，ProductID 来自 Inv_PurchaseCreditDetails  GROUP   BY  P. roductID）credit  ON  P.ID = credit.ProductID 
  LEFT   JOIN （选择 SUM（数量） as  ReturnQuantity，ProductID 来自 Inv_PurchaseReturnDetails  GROUP   BY  ProductID）Oreturn  ON  P.ID = Oreturn.ProductID 
 
 结束 

TABLE tbl1

ItemId Name            Quantity
1      Tomatoes        2    
2      Cucumbers       5
3      Oranges         3    
3      Oranges         1    


Table tbl2

ItemId  Name            Quantity
1       Tomatoes        3    
2       Cucumbers       5    
2       Cucumbers       10  
3       Oranges         2  

Table tbl3

ItemId  Name            Quantity
1       Tomatoes        1
1       Tomatoes        2    
2       Cucumbers       5
3       Oranges         2      

and want to output (tbl1+tbl2-tbl3) as a new temp table in stored procedure

ItemId  Name            TQuantity
1       Tomatoes        2
2       Cucumbers       15    
3       Oranges         4 

解决方案

This looks like homework so I will not give you a complete solution. Furthermore I will assume you are on SQL server.

1) Create a stored procedure[^], easy.

2) Use UNION ALL[^] to concatenate two outputs of the same structure.
Hint: How do you subtract qauntities in tbl3?

3) Use SUM[^] aggregate function to add the quantities.
Hint: What do you need to put in GROUP BY[^] clause?

4) Insert the results into a temporary table using SELECT INTO[^]. Temporary table name starts with a hash symbol (#).
Hint: How do you make the table visible outside your stored procedure?

Cheers

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[sp_rpt_StockSummary]

AS
Begin

SELECT P.ID, P.ProductName,P.ProductCode,COALESCE(cash.CashQuantity,0) + COALESCE(credit.CreditQuantity,0)- COALESCE(Oreturn.ReturnQuantity,0) as TQty

from dbo.Inv_ProductName P
	 

LEFT JOIN (select SUM(Quantity) as CashQuantity, ProductID from Inv_PurchaseCashDetails GROUP BY ProductID ) cash ON P.ID = cash.ProductID
LEFT JOIN (select SUM(Quantity) as CreditQuantity, ProductID from Inv_PurchaseCreditDetails GROUP BY ProductID) credit ON P.ID = credit.ProductID
LEFT JOIN (select SUM(Quantity) as ReturnQuantity, ProductID from Inv_PurchaseReturnDetails GROUP BY ProductID) Oreturn ON P.ID = Oreturn.ProductID

End

这篇关于如何在存储过程中计算此表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！