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

最新推荐文章于 2022-08-19 10:08:10 发布

原创最新推荐文章于 2022-08-19 10:08:10 发布 · 1.9k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#date #c

Oracle PL/SQL编程专栏收录该内容

103 篇文章

订阅专栏

本文通过具体案例展示了如何使用SQL来统计指定日期及其之前的所有数量总和。利用WITH语句创建临时表，并采用窗口函数进行累计求和，最终实现按日期和ID分组的数据展示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

问题：更具所给数据，统计当天的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