生成視圖，統計字段的問題，開新貼加重分提問
表1
id Original
1 10
2 20
表2
id 表1id Actua
1 1 2
2 1 1
3 1 3
4 2 5
5 2 10
視圖
Original$ Actua$ Balance$
10
2 8
1 7
3 4
20
5 15
10 5
數據庫里面只有Original跟Actua這兩個字段，Balance是計算出來的。也就是Balance=Original-Actua這樣循環的減下去，得Balance這個值，再用Balance再減Actua又得Balance我想用視圖來表示上面哪樣的表怎么表示，
謝謝
[解决办法]
Create View V_TEST
As
Select TOP 100 Percent Original, Actua, Balance From
(
Select id, 0 As 表2id, Original, 0 As Actua, 0 As Balance From 表1
Union All
Select 表1id, id, 0, Actua, A.Original - (Select SUM(Actua) From 表2 Where id <= B.id) As Balance
From 表1 A Inner Join 表2 B On A.id = B.表1id
) A
Order By id, 表2id
GO
[解决办法]
--上面有些bug，用下面的
--建立測試環境
Create Table 表1
(idInt,
OriginalInt)
Insert 表1 Select 1, 10
Union All Select 2, 20
Create Table 表2
(idInt,
表1idInt,
ActuaInt)
Insert 表2 Select 1, 1, 2
Union All Select 2, 1, 1
Union All Select 3, 1, 3
Union All Select 4, 2, 5
Union All Select 5, 2, 10
GO
--建立視圖
Create View V_TEST
As
Select TOP 100 Percent Original, Actua, Balance From
(
Select id, 0 As 表2id, Original, 0 As Actua, 0 As Balance From 表1
Union All
Select 表1id, B.id, 0, Actua, A.Original - (Select SUM(Actua) From 表2 Where id <= B.id And 表1id = B.表1id) As Balance
From 表1 A Inner Join 表2 B On A.id = B.表1id
) A
Order By id, 表2id
GO
--測試
Select * From V_TEST
GO
--刪除測試環境
Drop Table 表1, 表2
Drop View V_TEST
--結果
/*
OriginalActuaBalance
1000
028
017
034
2000
0515
0105
*/
[解决办法]
怎麼了？不符合你的要求還是怎樣？
