获取表中的上一个值 [英] get Previous Value in Table

问题描述

亲爱的，

如何获得以前的价值。



数据

 ITEMCODE日期问题数量
 1 10/10/2015 20 
 1 11/10/2015 0 
 1 13/10/2015 30 
 1 15/10 / 2015 30 
 1 16/10/2015 0 
 1 17/10/2015 0 
 1 118/10/2015 20 

我需要更换或更改0发行数量到上一个值，如下所示。

 ITEMCODE日期发行数量
 1 10 / 10/2015 20 
 1 11/10/2015 20 
 1 13/10/2015 30 
 1 15/10/2015 30 
 1 16/10/2015 30 
 1 17/10/2015 20 
 1 118/10/2015 20 

请帮我解决这个？

解决方案

如果我理解你的问题，请在更新声明中nt从前一行获取值。所以也许类似

 更新 MyTable 
  SET  [ISSUE QTY] =（ SELECT  b。[ISSUE QTY]）
  FROM  MyTable b 
  WHERE  b。[日期] =（ SELECT  MAX（c。[日期]）
  FROM  MyTable c 
  WHERE  c。[日期]< b。[日期] 
  AND  c。[ISSUE QTY]<>  0 ））
  WHERE  [ISSUE QTY] =  0 ; 

您需要更新ISSUE QTY为零的记录，尝试以下SQL

   UPDATE table_name SET [ISSUE QTY] = @ 0 WHERE [ISSUE QTY] = 0; 

然后使用方法SQL参数（基于此处使用的语言，C＃或VB.NET或您的平台的任何语言）和将@ 0的值更改为需要存在的值;根据您的数据，30或20。没有逻辑可以用来为你提供答案。



SQL参数化允许您保护您的应用程序免受SQL注入，这主要是由字符串连接和其他类似的查询构建过程引起的。

这种情况的最佳解决方案是在一个中使用UPDATE + SELECT语句！

  UPDATE  destTable 设置 Field1 = sourceTable.Field2 
  FROM 表1 作为 destTable  INNER   JOIN 表2  AS  sourceTable  ON  destTable.KeyField = sourceTable.ForeignKeyField 
  WHERE  destTable.FilterField =  @ Whatever  

根据需要更改代码！

Dear All,
How to Get Previous Value in table.

Data

ITEMCODE  Date           ISSUE QTY 
1         10/10/2015       20
1         11/10/2015        0
1         13/10/2015       30
1         15/10/2015       30
1         16/10/2015        0 
1         17/10/2015        0
1         118/10/2015      20

I need to replace or change 0 issue qty into Previous Value like below.

ITEMCODE  Date           ISSUE QTY 
1         10/10/2015       20
1         11/10/2015       20
1         13/10/2015       30
1         15/10/2015       30
1         16/10/2015       30 
1         17/10/2015       20
1         118/10/2015      20

Please help me how to fix this?

解决方案

If I understand your question correctly, in an update statement you want to fetch the value from the previous row. So perhaps something like

UPDATE MyTable
SET [ISSUE QTY] = (SELECT b.[ISSUE QTY])
                   FROM MyTable b
                   WHERE b.[Date] = (SELECT MAX(c.[Date])
                                     FROM   MyTable c
                                     WHERE  c.[Date] < b.[Date]
                                     AND    c.[ISSUE QTY] <> 0))
WHERE [ISSUE QTY] = 0;

You need to update the record where the ISSUE QTY is zero, try the following SQL

"UPDATE table_name SET [ISSUE QTY] = @0 WHERE [ISSUE QTY] = 0";

Then use the methods SQL Parameters (based on the language being used here, C# or VB.NET or what ever language for your platform) and change the value of @0 to a value that needs to be there; either 30 or 20 as per your data. There is no logic that I can use to give you an answer for that.

SQL Parameterization allows you to protect your application from SQL Injections, that are mostly caused by string concatenations and other similar query building procedures.

The best solution for such of situation is to use UPDATE + SELECT statement in one!

UPDATE destTable Set Field1 = sourceTable.Field2
FROM Table1 As destTable INNER JOIN Table2 AS sourceTable ON destTable.KeyField = sourceTable.ForeignKeyField
WHERE destTable.FilterField = @Whatever

Change the code to your needs!

这篇关于获取表中的上一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！