对于每日的交易额,要做截止到每日的累计和,这样的需求也很常见。在sql cookbook中有介绍,

select e.ename, e.sal,
   (select sum(d.sal) from emp d
         where d.empno <= e.empno) as running_total
from emp e

对于同样一个表,采用不同的别名e, d来处理,以empno为条件,保证截止到当前的记录号。

而对于我的应用,又更复杂了一些,必须按照每日累计,因为要照顾到可能有日期没有交易额的情况发生。为此,

select r.lxrq, coalesce(t.lze, 0.00) as lze from (
              select cast('20081001' as date) + s.a as lxrq
                from generate_series(0,
                         cast('20081015' as date)
                        -cast('20081001' as date),1)
                      as s(a) ) as r
              left join (
              select yxn, jyr, sum(pdysk+zdysk) as lze from nps_card
                where yxn=2009
                  and jyr>='20081001' and jyr<='20081015'
                group by yxn, jyr
                order by yxn, jyr asc
              ) as t
              on r.lxrq=t.jyr

这是确保每日都有记录,即使是没有交易记录的情况。下面就是累计求和

select e.lxrq, e.lze, (select sum(d.lze) from (
               select r.lxrq, coalesce(t.lze, 0.00) as lze from (
                  select cast('20080901' as date) + s.a as lxrq
                    from generate_series(0,
                             cast('20080930' as date)
                            -cast('20080901' as date),1)
                          as s(a) ) as r
                  left join (
                  select yxn, jyr, sum(pdysk+zdysk) as lze from nps_card
                    where yxn=2009
                      and jyr>='20080901' and jyr<='20080930'
                    group by yxn, jyr
                    order by yxn, jyr asc
                  ) as t
                  on r.lxrq=t.jyr ) as d where d.lxrq<=e.lxrq ) as ljlze
             from (
             select r.lxrq, coalesce(t.lze, 0.00) as lze from (
                  select cast('20080901' as date) + s.a as lxrq
                    from generate_series(0,
                             cast('20080930' as date)
                            -cast('20080901' as date),1)
                          as s(a) ) as r
                  left join (
                  select yxn, jyr, sum(pdysk+zdysk) as lze from nps_card
                    where yxn=2009
                      and jyr>='20080901' and jyr<='20080930'
                    group by yxn, jyr
                    order by yxn, jyr asc
                  ) as t
                  on r.lxrq=t.jyr) as e

看过去是有些复杂,不过有一些重复动态生成的表。

还有复杂的,这是当年度的,同时要生成上年度的同期累计求和,sql又要拉得很长了。pgsql赋予了sql的灵活性,确实很方便,自己也是乱用,有无更好的用法,请朋友们提出,呵呵。