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

INSERT INTO A(ID,DATE,COUNT) VALUES('A1','20150101','200')
INSERT INTO A(ID,DATE,COUNT) VALUES('A1','20150102','200')
INSERT INTO A(ID,DATE,COUNT) VALUES('A1','20150103','300')
INSERT INTO A(ID,DATE,COUNT) VALUES('A1','20150104','100')
INSERT INTO A(ID,DATE,COUNT) VALUES('B1','20150101','100')
INSERT INTO A(ID,DATE,COUNT) VALUES('B1','20150102','300')

INSERT INTO B(ID,COUNT) VALUES('A1','500')
INSERT INTO B(ID,COUNT) VALUES('B1','300')

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
from A t2, B t3
WHERE t2.ID=t3.ID
ORDER BY t2.ID,t2.Date

过滤出value大于0的

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 ,CASE WHEN (select sum(t1.count) from A t1 where DATE 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 DATE0
ORDER BY t2.ID,t2.Date

你在优化下哈

间顺序查出抵消后的数据，这样的SQL语句怎么写，不要存储过程