统计今天和今天之前的数量

问题：更具所给数据，统计当天的qty和当天之前的qty

解法：

with a as( select 'A' id, to_date('20110301','yyyymmdd') misdte, 2 qty from dual union all select 'A' id, to_date('20110302','yyyymmdd'), 1 from dual union all select 'A' id, to_date('20110303','yyyymmdd'), 4 from dual union all select 'A' id, to_date('20110302','yyyymmdd'), 1 from dual union all select 'A' id, to_date('20110304','yyyymmdd'), 2 from dual union all select 'A' id, to_date('20110305','yyyymmdd'), 7 from dual union all select 'A' id, to_date('20110302','yyyymmdd'), 1 from dual union all select 'A' id, to_date('20110305','yyyymmdd'), 2 from dual union all select 'A' id, to_date('20110305','yyyymmdd'), 2 from dual union all select 'B' id, to_date('20110301','yyyymmdd'), 3 from dual union all select 'B' id, to_date('20110301','yyyymmdd'), 9 from dual union all select 'B' id, to_date('20110301','yyyymmdd'), 3 from dual union all select 'B' id, to_date('20110303','yyyymmdd'), 17 from dual) select id, misdte, sum_qty, lag(sum_qty1, 1, 0) over(partition by id order by id, misdte) sum_qty1 from (select id, misdte, sum_qty, sum(sum_qty) over(partition by id order by id, misdte) sum_qty1 from (select id, misdte, sum(qty) sum_qty from a group by id, misdte order by 1, 2)); ID MISDTE SUM_QTY SUM_QTY1 -- ----------- ---------- ---------- A 2011-3-1 2 0 A 2011-3-2 3 2 A 2011-3-3 4 5 A 2011-3-4 2 9 A 2011-3-5 11 11 B 2011-3-1 15 0 B 2011-3-3 17 15

原帖：http://topic.youkuaiyun.com/u/20110327/15/6c3a2f34-6a5b-4e38-bb27-132b78d2ad31.html?88830