Perform arithmetic in select statement

Let's suppose I have balance 2000, and want to select balance as

balance=balance-Cr+Dr

So my balance column will give values as below.

balance        DR            Cr
40000          0             60开发者_如何学运维000  
100000         60000         0
0              0             100000

How is this possible in SQL query?

Please check similar question like me enter link description here

Here is a recursive CTE that calculates the balance using the balance from the previous row. You need something that defines the order of the rows. I use the ID column in the sample table.

-- Test table
declare @T table
(
  ID int identity primary key,
  DR int,
  Cr int
)

-- Sample data
insert into @T (DR, Cr)
select 0,     60000  union all
select 60000, 0      union all
select 0,     100000

-- In value
declare @StartBalance int
set @StartBalance = 100000

-- Recursive cte calculating balance as a running sum
;with cte as
(
  select 
    T.ID,
    @StartBalance - T.Cr + T.DR as Balance,
    T.DR, 
    T.Cr
  from @T as T
  where T.ID = 1
  union all
  select 
    T.ID,
    C.Balance - T.Cr + T.DR as Balance,
    T.DR, 
    T.Cr
  from cte as C
    inner join @T as T
      on C.ID+1 = T.ID  
)
select Balance, DR, Cr
from cte
option (maxrecursion 0)

Result:

Balance     DR          Cr
----------- ----------- -----------
40000       0           60000
100000      60000       0
0           0           100000

This should work:

SELECT (T.BALANCE-T.CR+T.DR) as "Balance", T.DR, T.CR
FROM <table-name> T

If you use Oracle, there is a function called LAG to reach the previous row data: http://www.adp-gmbh.ch/ora/sql/analytical/lag.html

If you read this link I think you will see that this is exactly what you need. But only if you use Oracle..