如何使用列选择查询更新sql中的变量值

Question

我想用列选择来更新用户变量。我得到了这样的错误

SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations

我需要这样的报告:这里贷方和借方表列。余额是用户列

表值

​

​

预期结果

​

我试过像这样的代码

Declare @Openingbalance decimal(18,8) set @Openingbalance=100 select  CreditAmount, DebitAmount, (@Openingbalance + CreditAmount-DebitAmount) as 'Balance' from ACT_Transaction

这个结果显示错误。因为在select语句中未更新期初余额

Answer 1

这应该适用于SQL 2008及更高版本

Declare @Openingbalance decimal(18,8) = 100
select CreditAmount, 
       DebitAmount, 
       (@Openingbalance + CreditAmount-DebitAmount) as 'Balance' 
from ACT_Transaction

Answer 2

Declare @Openingbalance decimal(18,8) 
set @Openingbalance=100 
select  @Openingbalance as 'Openingbalance', CreditAmount
DebitAmount, (@Openingbalance + CreditAmount-DebitAmount) as 'Balance' from ACT_Transaction

Answer 3

从技术上讲，您的查询是正确的，但是您没有提到要将期初余额显示为单独的列还是在余额列中显示？

您可以使用子查询来执行以下操作

Declare @optBal Numeric(10)集合@optBal=100

select fldDate，fldTrxType，fldAmount，(select sum(convert(money，case when cramt -1 else 1 end) * cramt + @optBal) from tblTrx where fldDate <= trx.fldDate)作为tblTrx trx by fldDate的余额