數據-SQL按時間抵消的查詢語句

SQL按時間抵消的查詢語句

如何按時間順序用B表數據抵消A表數據
A表：
ID DATE MOUNT
A1 2015-01-01 200
A1 2015-02-02 200
A1 2015-03-10 300
A1 2015-03-20 100
B1 2015-01-02 100
B1 2015-02-25 300
B表
ID COUNT
A1 500
B1 300
得出如下結果：
ID DATE MOUNT
A1 2015-03-10 200
A1 2015-03-20 100
B1 2015-02-25 100
這樣按時間順序查出抵消後的數據，這樣的SQL語句怎麼寫，不要存儲過程

最佳回答：

 select t2.ID,t2.Date,t2.count
,(select sum(t1.count) from A t1 where DATE<=t2.DATE AND t1.ID=t2.ID) AS sumcount
,(select sum(t1.count) from A t1 where DATE<=t2.DATE AND t1.ID=t2.ID)-t3.count AS value
,(select sum(t1.count) from A t1 where DATE<t2.DATE AND t1.ID=t2.ID)-t3.count AS value2
,CASE WHEN (select sum(t1.count) from A t1 where DATE<t2.DATE AND t1.ID=t2.ID)-t3.count <0 THEN t2.count+((select sum(t1.count) from A t1 where DATE<t2.DATE AND t1.ID=t2.ID)-t3.count)
      ELSE t2.count
 END AS value3
from A t2, B t3
WHERE t2.ID=t3.ID AND ((select sum(t1.count) from A t1 where DATE<=t2.DATE AND t1.ID=t2.ID)-t3.count)>0
ORDER BY t2.ID,t2.Date