如何将临时变量设置为基于另一个表的值

admin

如何将临时变量设置为基于另一个表的值

sql

我正在使用AdventureWorks 2012数据库，但我对此感到非常困惑，

到目前为止，我有

alter proc pName
(
@TranID int
)
as
declare @AccountID int
declare @Entered datetime
declare @Type char
declare @Amount money
declare @Service money
declare @WithdrawalDecrease smallint
declare @WithdrawalCount smallint

set @AccountID = (select AccountID 
                  from Transactions
                  where TransID = @TranID)

set @WithdrawalCount = (select WithdrawalCount 
                        from Accounts
                        inner join Transactions on Transactions.AccountID = Accounts.AccountID
                        where Transactions.AccountID = @AccountID)

但是取值的变量我在做什么错呢？

阅读 202

2021-07-01

共1个答案

admin

语法没有错

SET @AccountID = (SELECT AccountID
                  FROM   Transactions
                  WHERE  TransID = @TranID)
SET @WithdrawalCount = (SELECT WithdrawalCount
                        FROM   Accounts
                               INNER JOIN Transactions
                                       ON Transactions.AccountID = Accounts.AccountID
                        WHERE  Transactions.AccountID = @AccountID)

但是在这里，您尝试将TransID = @
TranID的AccountID设置为@AccountID。如果您的交易记录表中@TranID有多行，则最后插入的值将分配给该变量，因此请尝试top 1与order by

SET @AccountID = (SELECT top 1 AccountID
                  FROM   Transactions
                  WHERE  TransID = @TranID order by column)

SET @WithdrawalCount = (SELECT top 1 WithdrawalCount
                        FROM   Accounts
                               INNER JOIN Transactions
                                       ON Transactions.AccountID = Accounts.AccountID
                        WHERE  Transactions.AccountID = @AccountID order by column)

2021-07-01