去年同期数据的比较方法？

Question

近期项目开发具有如下要求：
目前数据表中存储如下记录：
业务类别 征收机关  科目代码 征收日期 当日累计0001,1,17,20051006,1
0001,1,17,20051009,2
0001,1,17,20040910,3
0001,1,17,20041011,4
0001,1,17,20041014,5
0001,1,17,20050121,6希望能够输入指定的征收日期，完成如下信息的输出：
业务类别，征收机关，科目代码，当月征收金额，本年征收金额，本年上月止金额，上年同期征收金额，上年上月止征收金额其中：
当月征收金额为截至到指定日期的当月征收金额情况，
本年征收金额为本科目，征收机关及业务类别的到统计月度的本年度日累计的合计
本年上月止金额，为本年度截至到上个月度的日累计合计
上年同期征收金额为去年当月的日累计合计值
上年上月止征收金额为截止到去年上月（不含去年当月）的年度合计值情况。还请有这方面经验的朋友给点建议！谢谢

Answer 1

create table test (itype varchar2(10), depart varchar2(10), otype varchar2(10), odata varchar2(10), ocount number )insert into test values ('0001','1','17','20051006',1)
insert into test values ('0001','1','17','20051009',2)
insert into test values ('0001','1','17','20040910',5)
insert into test values ('0001','1','17','20041011',12)
insert into test values ('0001','3','17','20041014',11)
insert into test values ('0001','3','16','20051021',13)
insert into test values ('0002','1','26','20050225',10)
insert into test values ('0001','3','17','20051014',30)
commit select itype as 业务类别 , depart as 征收机关 , otype as 科目代码, 
sum(decode(substr(odata,0,6),to_char(&today,'yyyymm'),ocount,0)) as 当月征收金额 ,
sum(decode(substr(odata,0,4),to_char(&today,'yyyy'),ocount,0)) as 本年征收金额,
sum (case when odata < to_char(trunc(&today,'mm'),'yyyymmdd') and substr(odata,0,4) = to_char(&today,'yyyy') then ocount else 0 end) as 本年上月止金额 ,
sum(decode(substr(odata,0,6),to_char(to_char(&today,'yyyy')-1)||to_char(&today,'mm'),ocount,0)) as 上年同期征收金额,
sum(case when odata < to_char(add_months(trunc(&today,'mm'),-12),'yyyymmdd') and substr(odata,0,4) = to_char(&today,'yyyy')-1 then ocount else 0 end) as 上年上月止征收金额
from test 
where odata <= to_char(&today,'yyyymmdd')
group by itype, depart , otype

Answer 2

用分析函数很容易解决的，以下在9i下测试通过（指定的征收日期为2005-10-19）：
SQL> create table TEST_NEW
  2  (
  3    SORT    VARCHAR2(4),
  4    UNIT    NUMBER,
  5    CODE    NUMBER,
  6    IN_DATE DATE,
  7    NUM     NUMBER
  8  )
  9  /Table createdinsert data.....SQL> select * from test_new;SORT       UNIT       CODE IN_DATE            NUM
---- ---------- ---------- ----------- ----------
0001          1         17 2005-10-6            1
0001          1         17 2005-10-9            2
0001          1         17 2005-9-9             3
0001          1         17 2005-10-20           4
0001          1         17 2004-10-11           5
0001          1         17 2004-9-20            6
0001          1         17 2005-1-11            7
0001          1         17 2005-3-6             8
0001          1         17 2003-9-11            9
0001          1         17 2003-8-11           12
0001          1         17 2003-10-11          13
0002          1         12 2005-10-11          1412 rows selectedSQL> 
SQL> SELECT SORT,
  2         unit,
  3         code,
  4         in_date,
  5         num,
  6         SUM(num) over(PARTITION BY SORT,unit,code ORDER BY trunc(in_date, 'mm') RANGE CURRENT ROW) "当月征收金额",
  7         SUM(num) over(PARTITION BY SORT,unit,code ORDER BY trunc(in_date, 'yyyy') RANGE CURRENT ROW) "本年征收金额",
  8         SUM(num) over(PARTITION BY SORT,unit,code ORDER BY trunc(in_date, 'yyyy') RANGE CURRENT ROW) - SUM(num) over(PARTITION BY SORT,unit,code ORDER BY trunc(in_date, 'mm') RANGE CURRENT ROW) "本年上月止金额",
  9         SUM(num) over(PARTITION BY SORT,unit,code ORDER BY trunc(in_date, 'mm') RANGE BETWEEN INTERVAL '1' YEAR preceding AND INTERVAL '1' YEAR preceding) "上年同期征收金额",
 10         SUM(num) over(PARTITION BY SORT,unit,code ORDER BY trunc(in_date, 'yyyy') RANGE BETWEEN INTERVAL '1' YEAR preceding AND UNBOUNDED FOLLOWING) - SUM(num) over(PARTITION BY SORT,unit,code ORDER BY trunc(in_date, 'mm') RANGE BETWEEN INTERVAL '1' YEAR preceding AND UNBOUNDED FOLLOWING) "上年上月止征收金额"
 11  FROM   test_new t
 12  WHERE  in_date <= DATE '2005-10-19'
 13  /SORT UNIT       CODE IN_DATE      NUM 当月 本年 本年上月止   上年同期 上年上月止
---- ---- ---------- ----------- ---- ---- ---- ---------- ---------- ----------
0001    1         17 2003-8-11     12   12   34         22                     0
0001    1         17 2003-9-11      9    9   34         25                     0
0001    1         17 2003-10-11    13   13   34         21                     0
0001    1         17 2004-9-20      6    6   11          5          9         12
0001    1         17 2004-10-11     5    5   11          6         13         21
0001    1         17 2005-1-11      7    7   21         14                     0
0001    1         17 2005-3-6       8    8   21         13                     0
0001    1         17 2005-9-9       3    3   21         18          6          0
0001    1         17 2005-10-6      1    3   21         18          5          6
0001    1         17 2005-10-9      2    3   21         18          5          6
0002    1         12 2005-10-11    14   14   14          0                     011 rows selected

Answer 3

因为使用分析函数，以上数据包含日统计数据，可以作为视图使用，如果要过滤数据只须在外面嵌套一层查询就可以了，比如只查询2005年每个月（去除明细）的数据：select distinct SORT,unit,code ,to_char(in_date,'yyyy-mm'),当月,本年,本年上月止... from (query) where to_char(in_date,'yyyymm') = '2005'

Answer 4

select a.*,
       b.thisMonth_thisYear,c.thisYear_thisYear,d.lastMOnth_thisYear,e.lastYear_thisMonth,f.lastYear_lastMonth
from
(
 select distinct itype,depart, otype from testdata
)
a,
(
select itype, depart,otype,sum(ocount) thisMonth_thisYear from testdata where oTime < to_date('2005-10-19','yyyy-mm-dd') and trunc(oTime,'month') = trunc(to_date('2005-10-19','yyyy-mm-dd'),'month') group by itype, depart,otype
)
b,
(
select itype, depart,otype,sum(ocount) thisYear_thisYear from testdata where oTime < to_date('2005-10-19','yyyy-mm-dd') and trunc(oTime,'year') = trunc(to_date('2005-10-19','yyyy-mm-dd'),'year') group by itype, depart,otype
)
c,
(
select itype, depart,otype,sum(ocount) lastMonth_thisYear from testdata where oTime < trunc(to_date('2005-10-19','yyyy-mm-dd'),'month') and trunc(oTime,'year') = trunc(to_date('2005-10-19','yyyy-mm-dd'),'year') group by itype, depart,otype
)
d,
(
select itype, depart,otype,sum(ocount) lastYear_thisMonth from testdata where trunc(oTime,'year') < trunc(to_date('2005-10-19','yyyy-mm-dd'),'year') and oTime > to_date('2005-10-19','yyyy-mm-dd')-400  and to_char(oTime,'mm') = to_char(to_date('2005-10-19','yyyy-mm-dd'),'mm') group by itype, depart,otype
)
e,
(
select itype, depart,otype, sum(ocount) lastYear_lastMonth from testdata where trunc(oTime,'year') < trunc(to_date('2005-10-19','yyyy-mm-dd'),'year') and oTime > to_date('2005-10-19','yyyy-mm-dd')-430  and to_char(oTime,'mm') = to_char(trunc(to_date('2005-10-19','yyyy-mm-dd'),'month')-1,'mm') group by itype, depart,otype
)
f
where
a.itype=b.itype(+) and a.depart=b.depart(+) and a.otype=b.otype(+) and
a.itype=c.itype(+) and a.depart=c.depart(+) and a.otype=c.otype(+) and
a.itype=d.itype(+) and a.depart=d.depart(+) and a.otype=d.otype(+) and
a.itype=e.itype(+) and a.depart=e.depart(+) and a.otype=e.otype(+) and
a.itype=f.itype(+) and a.depart=f.depart(+) and a.otype=f.otype(+)

Answer 5

唉，各位都是大牛，学习，SQL到用时方很少！