我被困在如何循环行集并保存在变量中。
请注意,这可能是伪代码,因为SQL不是我的专长。
@all_customers = select CustNum from [crrsql].[dbo].[Customer]; some loop(@all_customers as user) //I need to find out what the Acct_balance field is and either subtract or add to bring all the balances to 0 @balance = select Acct_balance from [crrsql].[dbo].[Customer] where CustNum = user; if @balance > 0 update [crrsql].[dbo].[Customer] set Acct_balance = 0; INSERT INTO [crrsql].[dbo].[AR_Transactions] (cashier_ID, CustNum, Balance) VALUES (100199, user, @balance); else update [crrsql].[dbo].[Customer] set Acct_balance = 0; INSERT INTO [crrsql].[dbo].[AR_Transactions] (cashier_ID, CustNum, Balance) VALUES (100199, user, "-" + @balance); end end loop
如您所见,我正在遍历客户,在该循环中,我需要获取当前余额并将其设置为零,但是首先我需要确定它是正数还是负数,以便能够确定插入的是AR_Transactions表中的每个用户都必须为正数或负数。您能帮忙解决遗失的物品吗?
这是将原始代码直接转换为有效的SQL Server语法的方法。我无法谈论围绕您正在做的事情的业务规则,但是请注意,这种方法避免了使用游标,而是使用ABS()函数消除了原始的if / else块。
declare @all_customers as table( customernumber int ); /* --you can insert dummy data for testing purposes like so: insert into @all_customers select 5, 1 union select 2, 1 --*/ while (0 < (select count(*) from @all_customers)) begin declare @current_customer int = (select top 1 customernumber from @all_customers); declare @balance money = (select acct_balance from [crrsql].[dbo].[Customer] where CustNum = @current_customer); update [crrsql].[dbo].[Customer] set Acct_balance = 0; INSERT INTO [crrsql].[dbo].[AR_Transactions] (cashier_ID, CustNum, Balance) VALUES (100199, user, abs(@balance)); delete @all_customers where customernumber = @current_customer; end