with t (rq, sl, pid) as (
select to_date('2012-9-1','yyyy-mm-dd'), 50, 1 from dual union all
select to_date('2012-9-1','yyyy-mm-dd'), 30, 2  from dual union all
select to_date('2012-8-31','yyyy-mm-dd'), 45, 1  from dual union all
select to_date('2012-8-31','yyyy-mm-dd'), 36, 2  from dual)
select * from (
select rq, sl,
sl-(select sl from t t1 where t1.rq=t.rq-1 and t1.pid=t.pid) bh, pid from t)
where bh is not null;

------解决方案--------------------
托大了,我以为这个SQL可以直接查询出来!汗....
SQL code

WITH FOO AS(
 SELECT '2012-9-1' AS RQ,50 AS SL,1 AS PID FROM DUAL
 UNION
 --这个地方把LZ的RQ修改成9-2,否则sql会出错
 SELECT '2012-9-2' AS RQ,30 AS SL,2 AS PID FROM DUAL
 UNION
 SELECT '2012-8-31' AS RQ,45 AS SL,1 AS PID FROM DUAL
 UNION
 SELECT '2012-8-31' AS RQ,36 AS SL,2 AS PID FROM DUAL
)
SELECT MPID,SUM(ASL)-SUM(BSL) AS BH,PID
FROM (
SELECT MAX(RQ) OVER(PARTITION BY PID ORDER BY PID) AS MPID, 
   CASE WHEN MAX(RQ) OVER(PARTITION BY PID ORDER BY PID) = RQ THEN SL ELSE 0 END AS ASL,
   CASE WHEN MAX(RQ) OVER(PARTITION BY PID ORDER BY PID)!= RQ THEN SL ELSE 0 END AS BSL,
    PID
  FROM FOO
) O GROUP BY PID,MPID

------解决方案--------------------
求助，帮顶

------解决方案--------------------
--试试LAG函数
--rq sl pid
with t as (
select date'2012-9-1' as fdate, 50 as QUANTITY, 1 as fid from dual
union all
select date'2012-9-1', 30, 2 from dual
union all
select date'2012-8-31', 45, 1 from dual
union all
select date'2012-8-31', 36, 2 from dual
)
select fdate,quantity,fid,lag(QUANTITY)over(partition by fid order by fdate) as lastDay from t

FDATE                     QUANTITY               FID                    LASTDAY               
------------------------- ---------------------- ---------------------- ----------------------  
2012-08-31 00:00:00       45                     1                                            
2012-09-01 00:00:00       50                     1                      45                    
2012-08-31 00:00:00       36                     2                                            
2012-09-01 00:00:00       30                     2                      36                    



------解决方案--------------------
WITH t AS
 ( SELECT DATE'2012-9-1' AS fdate, 50 AS QUANTITY, 1 AS fid FROM dual
 UNION ALL
 SELECT DATE'2012-9-1', 30, 2 FROM dual
 UNION ALL
 SELECT DATE'2012-8-31', 45, 1 FROM dual
 UNION ALL
 SELECT DATE'2012-8-31', 36, 2 FROM dual
 )
SELECT fdate,
 quantity,
 fid,
 lastday,
 DECODE(lastday,NULL,quantity,quantity-lastday) fresult
FROM
 (SELECT fdate,
   quantity,
   fid,
   lag(QUANTITY)over(partition BY fid order by fdate) AS lastDay
 FROM t
 )

FDATE                     QUANTITY               FID                    LASTDAY                FRESULT               
------------------------- ---------------------- ---------------------- ---------------------- ----------------------  
2012-08-31 00:00:00       45                     1                                             45                    
2012-09-01 00:00:00       50                     1                      45                     5                     
2012-08-31 00:00:00       36                     2                                             36