存储过程中的循环 [英] loop in Stored Procedure

问题描述

我有一个名为SpDet的表有pcode（产品代码），数量（数量），vtype（凭证类型销售/购买），spdt（交易日期）费率，amt等现在问题是我找到了收盘股票特定日期之间的所有产品。尽管我已经完成了单件产品，但我无法为所有产品做到这一切，你们都可以帮助我!!!!



< b> Sql存储过程

 创建 程序 [dbo]。[stocktrial3]  @ dtfrom   date ， @ dtto   date  
 
  as  
 
 开始 
 
  SET   NOCOUNT   ON ; 
 
 声明  @ inqty   int ， @outqty   int ， @ opinionqty   int ， @ opoutqty   int  ， @ opqty   int ， @ opclqty  < span class =code-keyword> int ， @ clqty   int  
 
 选择 @ opqty = opqty 来自项目订单  by  PCode 
 
  select   @ opinqty  = SUM（qty）来自 SPDet 其中​​ Vtype = '  PU' 和 SPDt<  @ dtfrom   group   by  PCode，Vtype < span class =code-keyword> order   by  PCode 
 
  select   @ opoutqty  = SUM（qty）来自 SPDet 其中 Vtype  （'  SP'，'  CM'）和 SPDt<  @ dtfrom   group   by  PCode，Vtype < span class =code-keyword> order   by  PCode 
 
  select   @inqty  = SUM（qty）来自 SPDet 其中 Vtype = '  PU' 和 SPDt    @ dtfrom  和  @ dtto   group   by  PCode，Vtype < span class =code-keyword> order   by  PCode 
 
  select   @outqty  = SUM（qty）来自 SPDet 其中​​ Vtype  in （'  SP'，'  CM' ）和 SPDt    @ dtfrom  和  @ dtto   group   by  PCode，Vtype  order   by  PCode 
 
  set  @ opclqty = @ opqty + ISNULL（ @ opinionqty ， 0 ） -  ISNULL（ @ opoutqty ， 0 ）
 
  set  @ clqty = ISNULL（ @ opclqty ， 0 ）+ ISNULL（ @ inqty ， 0 ） -  ISNULL（  @outqty ， 0 ）
 
 选择 Item.Pname， @ opclqty   as  PopQty，ISNULL（ @inqty ， 0 ） as  PinQty，ISNULL（ @outqty ， 0 ） as  POutQty， @ clqty   as  PClqty 
 
 来自项目订单 按 PCode 
 
 结束 

结果

 PName Op.Qty InQty OutQty Clqty 
 V. AIO 20 0 5 15 
 Lenevo笔记本电脑20 0 5 15 
 LCD 3220 0 5 15 
 LED 3220 0 5 15 
 SS 20 0 5 15 
 DD 20 0 5 15 
 Pendrive 20 0 5 15 
 Raid&裁缝20 0 5 15 
笔记本电脑20 0 5 15 
标签20 0 5 15 
 Lenevo桌面20 0 5 15 

您可以看到结果是所有产品都相同。

解决方案

问题没有明确解释。对于SQL存储过程中的循环，请参阅，例如：

在存储过程中使用for循环 [ ^ ]，

http://stackoverflow.com/questions/16115043/sql-stored-procedure-while-loop [ ^ ]。

-SA

哇！多么可怕的解决方案！抱歉。



要实现您的需求，请使用汇总功能 [ ^ ]，例如：

  SELECT 产品，SUM（质量） AS  SumOfQty 
  FROM  TableName 
  GROUP   BY 产品
  WHERE  DateField  BETWEEN  < span class =code-sdkkeyword> @start   AND   @ end  

在某些情况下，数据透视表 [ ^ ]可能会有所帮助。

您可以使用公用表表达式 [ ^ 也是。

I have table called SpDet having pcode(product code),qty(quantity),vtype(voucher type sale/purchase) ,spdt(transaction date)rate ,amt etc.Now the problem is i have find the closing stock of all products between a specific date.Though i have done it for single item but i can not do it for all products can you all help me !!!!

Sql Stored Procedure

create procedure [dbo].[stocktrial3] @dtfrom date,@dtto date

as

begin

SET NOCOUNT ON ;

declare @inqty int,@outqty int,@opinqty int,@opoutqty int,@opqty int,@opclqty int,@clqty int

select @opqty=opqty from Item order by PCode

select @opinqty = SUM(qty) from SPDet where Vtype='PU' and SPDt < @dtfrom  group by PCode,Vtype order by PCode

select @opoutqty =SUM(qty) from SPDet where Vtype in ('SP','CM') and SPDt < @dtfrom  group by PCode,Vtype order by PCode

select @inqty =SUM(qty) from SPDet where Vtype='PU' and SPDt between @dtfrom and @dtto  group by PCode,Vtype order by PCode

select @outqty =SUM(qty) from SPDet where Vtype in ('SP','CM') and SPDt between @dtfrom and @dtto  group by PCode,Vtype order by PCode

set @opclqty=@opqty+ISNULL(@opinqty,0)-ISNULL(@opoutqty,0)

set @clqty=ISNULL(@opclqty,0)+ISNULL(@inqty,0)-ISNULL(@outqty,0)

select Item.Pname,@opclqty as PopQty,ISNULL(@inqty,0) as PinQty,ISNULL(@outqty,0) as POutQty,@clqty as PClqty

 from Item order by PCode

end

Result

PName   Op.Qty  InQty  OutQty   Clqty
VAIO    20  0   5   15
Lenevo Laptop   20  0   5   15
LCD 32" 20  0   5   15
LED 32" 20  0   5   15
SS  20  0   5   15
DD  20  0   5   15
Pendrive    20  0   5   15
Raid & Tailor   20  0   5   15
Laptop  20  0   5   15
Tab 20  0   5   15
Lenevo Desktop  20  0   5   15

As u can see that result is all product is same.

解决方案

The problem is not clearly explained. For "looping" in SQL stored procedures, please see, for example:
using for loop in stored procedure[^],
http://stackoverflow.com/questions/16115043/sql-stored-procedure-while-loop[^].

—SA

Wow! What a terrible solution! Sorry.

To achieve that what you need, use aggregate functions[^], eg.:

SELECT Product, SUM(Quality) AS SumOfQty
FROM TableName
GROUP BY Product
WHERE DateField BETWEEN @start AND @end

In some cases, pivot table[^] might be helpful.
You are able to achieve that using Common Table Expressions[^] too.

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