请问sql

--库存表    t_storeitemid         materialid  totalAmount1           1001       100002           1002       200003           1003       30000--订单表t_orderticketid           materialid     needAmount1             1001          1002             1001          2503             1001          1504             1002          2005             1002          1506             1003          300--期望查询结果orderticketid materialid    remainAmount1              1001          100002              1001          99003              1001          96504              1002          200005              1002          198006              1003          30000

create table t_storeitem as select 1 id,1001 materialid,10000 totalAmount from dualunion select 2,1002,20000 from dualunion select 3,1003,30000 from dual;create table t_orderticket as select 1 id,1001 materialid,100 needAmount from dualunion select 2,1001,250 from dualunion select 3,1001,150 from dualunion select 3,1002,200 from dualunion select 3,1002,150 from dualunion select 3,1003,300 from dual;select rownum as orderticketid,a.materialid,lag(a.totalAmount-b.needAmount,1,a.totalAmount) over(partition by a.materialid order by a.id) as remainAmount from t_storeitem a,t_orderticket b where a.materialid=b.materialid
------解决方案--------------------
貌似这样可以？
SQL code
select t1.id,t1.materialid,t1.needAmount,       nvl(totalamount-(select sum(needAmount) from t_orderticket a where a.materialid=t1.materialid and a.id<t1.id),totalamount) cfrom t_orderticket t1,t_storeitem t2where t1.materialid=t2.materialidorder by t1.materialid,t1.id
------解决方案--------------------
楼上的那个，就很直观。
也可以改成这样（用开窗函数）：

  select o.id,
      o.materialid,
      o.needAmount,
      i.totalamount-sum(o.needAmount) over(partition by o.materialid order by o.id)+o.needamount
 from t_storeitem i, t_orderticket o
  where i.materialid = o.materialid
  order by o.materialid