如何根据SQL C＃中的条件递增每一行 [英] How do I increment each row according to condition in SQL C#

问题描述

我有一个来自textBox的值为100，我想在stock_out列中增加该值，但只有stock_out列等于stock_in列，如果第一行值为40，则第一行stock_out应增加10 100比100（90）的剩余值应该在它下面的行中递增，然后在它下面行直到需要（直到100为0）。物品按Item_Name和Bill No asc排序。

 Bill No Item_Name Stock_in Stock_out stock_in_hand 
 1 chicken 50 40 10 
 2 chicken 40 0 40 
 3 fish 100 30 70 
 4 fish 20 20 0 
 5 chicken 60 10 50 
 6 chicken 100 20 80 

要获得的结果。

 Bill No Item_Name Stock_in Stock_out stock_in_hand 
 1 chicken 50 50 0 
 2 chicken 40 40 0 
 5 chicken 60 60 0 
 6鸡100 20 80 
 3鱼100 30 70 
 4鱼20 20 0 

这是我能够的代码到目前为止，但它需要很多工作，我没有cte或任何复杂的c＃编码的专业知识。我正在使用SQL Server 2014和c＃。



PS我知道sql注入，我会将我的sql查询更改为参数化查询。

非常感谢任何帮助。



我尝试过：



C＃代码SQL查询

 选择 * 
 来自 stock_recieve 
 其中 Item_Name 喜欢 ' + comboBoxitem.Text +' 
 其中 Stock_out< Stock_in 
 订单 按 [Bill No]  asc ; 
 
  update  stock_recieve 
  set  Stock_out = Stock_out +   + qty + 
 其中 Stock_out< ; Stock_in; 

解决方案

  - 请尝试这样，如果不匹配$ b $，请替换您的表名和列名b DECLARE @Counter as int = 1 
声明@Stock as int = 100 
 
声明@StockRecieved作为表（Sr int，BillNo int）
插入@StockRecieved 
 SELECT ROW_NUMBER（）over（[Item_Name]，[Bill No]的顺序）Sr，[Bill No] 
 FROM [dbo]。[stock_recieve] 
 
 WHILE（@ Stock> 0 ）
 BEGIN 
声明@DiffInOut int 
声明@StockToBeUpdated int 
声明@BILLNo int 
从@StockRecieved选择@ BILLNo = BillNo，其中Sr = @ Counter 
选择@ DiffInOut = [Stock_in]  -  [Stock_out] FROM [dbo]。[stock_recieve]其中[Bill No] = @ BILLNo 
 
 if（@ Stock> = @ DiffInOut）
 BEGIN 
 SET @ StockToBeUpdated = @ DiffInOut 
 SET @ Stock = @ Stock- @ DiffInOut 
 END 
 ELSE 
 BEGIN 
 SET @ StockToBeUpdated = @ Stock 
 SET @ Stock = 0 
 END 
 
 UPDATE [stock_recieve]设置[Stock_out] = [Stock_out] + @ StockToBeUpdated，其中[Bill No] = @ BILLNo 
 
 SET @ Counter = @ Counter + 1; 
 END 
 
  - 确认
 SELECT * FROM [dbo]。[stock_recieve]订单[Item_Name]，[Bill No] 

I have a value of 100 from a textBox and I want to increment that value in the stock_out column but only till stock_out column is equal to stock_in column, if first row value is 40 than first row stock_out should be incremented by 10 from 100 than the remaining value from 100(90) should be incremented in the row below it and then row below it till needed (till 100 is 0). Items are ordered by Item_Name and Bill No asc.

Bill No   Item_Name   Stock_in  Stock_out  stock_in_hand
1          chicken       50        40           10
2          chicken       40         0           40
3          fish          100       30           70
4          fish          20        20            0
5          chicken       60        10           50
6          chicken       100       20           80

The result to be obtained.

Bill No   Item_Name   Stock_in  Stock_out  stock_in_hand
1          chicken       50        50           0
2          chicken       40        40           0
5          chicken       60        60           0
6          chicken       100       20          80
3          fish          100       30          70
4          fish          20        20           0

Here is the code that I have been able to do so far, but it needs a lot of work and I do not have expertise in cte or any complicated c# coding. I am using SQL Server 2014 and c#.

P.S I know about the sql injection and I will change my sql queries to parameterized queries.
Any help would be greatly appreciated.

What I have tried:

C# code SQL Query

select * 
from stock_recieve 
where Item_Name like'" + comboBoxitem.Text + "' 
where Stock_out < Stock_in 
order by [Bill No] asc;

update stock_recieve 
     set Stock_out = Stock_out+" + qty + "
where Stock_out < Stock_in;

解决方案

--Please Try This, replace your table and column name if not matching
DECLARE @Counter as int=1
	Declare @Stock as int=100

declare @StockRecieved as table(Sr int, BillNo int)
insert into @StockRecieved
SELECT ROW_NUMBER() over(order by [Item_Name], [Bill No]) Sr,[Bill No]     
  FROM [dbo].[stock_recieve] 

	WHILE(@Stock>0)
		BEGIN
		declare @DiffInOut int
		declare @StockToBeUpdated int
		declare @BILLNo int
select @BILLNo= BillNo from @StockRecieved where Sr=@Counter
select  @DiffInOut=[Stock_in]-[Stock_out] FROM [dbo].[stock_recieve] where [Bill No]=@BILLNo

		if (@Stock>=@DiffInOut)
			BEGIN
				SET @StockToBeUpdated=@DiffInOut
				SET @Stock=@Stock-@DiffInOut
			END
		ELSE
			BEGIN
			 SET @StockToBeUpdated=@Stock
			 SET @Stock=0
			END

		UPDATE [stock_recieve] set [Stock_out]=[Stock_out]+@StockToBeUpdated where [Bill No]=@BILLNo
		
		SET @Counter=@Counter+1;
		END

--To confirm
		SELECT *   FROM [dbo].[stock_recieve] order by [Item_Name], [Bill No]

这篇关于如何根据SQL C＃中的条件递增每一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！