SQL Server Design for displaying sequential accounts like bank accounts (please consult)

-- View created this time

Create Table MS (

[Fcid] [int] identity (1, 1) primary key not null, -- auto-increment ID

[M] [int] Null constraint [DF _ member_money _ M _ 0519c6af] default (0), -- in the event of an amount, the revenue is positive, and the consumption is negative

[D] [datetime] Null, -- Date and Time

[Des] [nvarchar] (50) Collate chinese_prc_ci_as null, -- Abstract

[Mid] [int] Null, -- member ID

[Ye] [int] Null, -- balance)

Go

Create proc pro_fc (@ mid int, @ M int, @ des varchar (50)

Declare @ d datetime

Set @ d = getdate ()

Insert into MS (MID, M, D, des) values (@ mid, @ M, @ D, @ des)

Declare @ I int

Set @ I = 0;

While (@ I <1000000)

Begin

Exec pro_fc 1001,1, 'recharged'

Exec pro_fc 1001,-1, 'Consumed'

Set @ I = @ I + 1

End

Create VIEW _ fl_view

Select mid as member ID, D as time, des as abstract, M amount, balance = isnull (ye, (select sum (m) from ms B where fcid <=. fcid and mid =. mid) from MS

 

Set statistics time on

Go

Select Top 1000 * From _ fl_view where Member id = 1001

Go

Select * From _ fl_view where mid = 1001

Set statistics time off;

-- Message

 

 

SQL Server execution time: CPU time = 0 ms, time consumed = 0 ms. SQL Server Analysis and Compilation Time: CPU time = 16 ms, time consumed = 17 ms. (The number of affected rows is 1000 rows) SQL Server execution time: CPU time = 922 milliseconds, time consumed = 961 milliseconds. SQL Server Analysis and Compilation Time: CPU time = 0 ms, time consumed = 0 ms.