循环遍历SQL Server中的记录集

Question

我被困在如何遍历行集并保存在变量中。

请注意，这可能是伪代码，因为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表中每个用户的插入是否需要是正数或负数。你能帮忙解决缺失的部分吗？

Answer 1

这里是直接翻译过来的原密码为有效的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 

Answer 2

我会看看cursors，但是IMO在应用程序逻辑中比在SQL中更好。

Answer 3

您应该可以在不使用游标或其他程序代码的情况下在几个语句中执行此操作。只要确保这一切都在一个交易：

BEGIN TRANSACTION 

INSERT INTO crrsql.dbo.AR_Transactions (
    cashier_id, 
    cust_num, 
    balance, 
    transaction_date) 
SELECT 
    100199, 
    cust_num, 
    -acct_balance, 
    DATEADD(MINUTE, -30, current_date) 
FROM crrsql.dbo.Customers 
WHERE acct_balance <> 0 

UPDATE crrsql.dbo.Customers SET acct_balance = 0 WHERE acct_balance <> 0 

COMMIT TRANSACTION 

当然，添加适当的错误处理，并确保先进行测试。

此外，我稍微改变了一些你的表和列的名称。我不想进入哪个特定的命名约定比其他命名约定更好，但在至少是一致的。如果你打算使用下划线，请使用它们。如果您打算使用驼峰符号，请使用该符号，但不要混用它们。复数与单数表名相同。

Answer 4

您正在寻找一个函数，如果@balance> 0，则返回@balance，否则 - @余额。

ABS（@balance）会这样做。 （除非你需要插入一个以“ - ”开头的字符串文字，但看起来很奇怪 - 我假设余额列是十进制类型。）

我猜在商业领域，但可能有也是您需要根据交易标志设置的信用卡/借记栏。在这种情况下，你可能想是这样的：

INSERT INTO [crrsql].[dbo].[AR_Transactions] (cashier_ID, CustNum, Balance, CR_DR) VALUES (100199, user, ABS(@balance), CASE WHEN @balance > 0 THEN 'CR' ELSE 'DR' END); 

Answer 5

作为一般规则，以为你需要在SQL做基于行的操作是，您（或他人）不正确地陷害问题的一个非常好的迹象：它代表了程序性而非集合性思维的代码味道。

你想要的伪代码，我认为，这些方针的东西：

-- Create a temporary table. A table starting with # is a temporary. It will be 
-- automatically dropped when the session ends. If two sessions creates temp 
-- tables with the same name, at the same time, they will still get one table each. 
create table #work 
(
    CustNum int , 
    Balance money , 
) 

insert #work (CustNum , Balance) 
select CustNum , Balance 
from Customer 
where Balance != 0 

begin transaction 

insert dbo.AR_Transactions (cashier_ID, CustNum, Balance) 
select cashier_ID = 100199 , 
     user  = CustNum , 
     adjustment = case sign(@balance) -- should always be +1 or -1 
        when 1 then @balance -- positive balance 
        when -1 then [email protected] -- negative balance 
        end 

update Customer set balance = 0 
from Customer c 
join #work w on w.CustNum = c.CustNum 

commit transaction 

-- Manual tidying up if the connection might be kept open. 
drop table #work 

Answer 6

对于SQL 2005和高达：

UPDATE C 
SET C.Acct_Balance = 0 
OUTPUT 100199, Inserted.CustNum, -Deleted.Acct_Balance, DateAdd(Minute, -30, GetDate()) 
INTO crrsql.dbo.AR_Transactions (Cashier_ID, CustNum, Balance, Transaction_Date) 
FROM crrsql.dbo.Customer C 
WHERE C.Acct_Balance <> 0 

因为有一些混乱，我会注意到，-Deleted.Acct_Balance是所有你需要带来的平衡为零。它否定了插入积极的负平衡，并且否定积极的平衡来插入否定。

对于SQL 2000，您需要多个语句。