如我有以下一个表,
test
id count ddate
.. .... .......
1 3 20070701
2 3 20070704
3 3 20070706
4 3 20070708
5 3 20070709
6 3 20070715
7 3 20070728
8 3 20070730
9 3 20070811
10 3 20070821
... .... .......
现在我给给一个月的开始和结束日期,统计该月和前11月没个月的count的和
格式像下面这样
本月 上一个月 上两个月 .... .... ....
test
id count ddate
.. .... .......
1 3 20070701
2 3 20070704
3 3 20070706
4 3 20070708
5 3 20070709
6 3 20070715
7 3 20070728
8 3 20070730
9 3 20070811
10 3 20070821
... .... .......
现在我给给一个月的开始和结束日期,统计该月和前11月没个月的count的和
格式像下面这样
本月 上一个月 上两个月 .... .... ....
from test a
where to_char(ddate,'yyyymm') ='200709'
group a.id,to_char(ddate,'yyyymm')
max(decode(zz.ddate,to_char(add_months(to_date('20070701','yyyymmdd'),-1),'yyyymm'),zz.amount,0)) as "Before one Month",
max(decode(zz.ddate,to_char(add_months(to_date('20070701','yyyymmdd'),-2),'yyyymm'),zz.amount,0)) as "Before two Month",
max(decode(zz.ddate,to_char(add_months(to_date('20070701','yyyymmdd'),-3),'yyyymm'),zz.amount,0)) as "Before three Month",
max(decode(zz.ddate,to_char(add_months(to_date('20070701','yyyymmdd'),-4),'yyyymm'),zz.amount,0)) as "Before four Month",
max(decode(zz.ddate,to_char(add_months(to_date('20070701','yyyymmdd'),-5),'yyyymm'),zz.amount,0)) as "Before five Month",
max(decode(zz.ddate,to_char(add_months(to_date('20070701','yyyymmdd'),-6),'yyyymm'),zz.amount,0)) as "Before six Month",
max(decode(zz.ddate,to_char(add_months(to_date('20070701','yyyymmdd'),-7),'yyyymm'),zz.amount,0)) as "Before seven Month",
max(decode(zz.ddate,to_char(add_months(to_date('20070701','yyyymmdd'),-8),'yyyymm'),zz.amount,0)) as "Before eight Month",
max(decode(zz.ddate,to_char(add_months(to_date('20070701','yyyymmdd'),-9),'yyyymm'),zz.amount,0)) as "Before night Month",
max(decode(zz.ddate,to_char(add_months(to_date('20070701','yyyymmdd'),-10),'yyyymm'),zz.amount,0)) as "Before ten Month",
max(decode(zz.ddate,to_char(add_months(to_date('20070701','yyyymmdd'),-11),'yyyymm'),zz.amount,0)) as "Before eleven Month"
from (
select to_char(tt.ddate,'yyyymm') as ddate,
sum(tt.count) as amount
from test tt
where to_char(tt.ddate,'yyyymm') >= to_char(add_months(to_date('20070701','yyyymmdd'),-11),'yyyymm')
and to_char(tt.ddate,'yyyymm') <= to_char(to_date('20070701','yyyymmdd'),'yyyymm')
group by to_char(tt.ddate,'yyyymm')
)zz;
This Month Before one Month Before two Month Before three Month Before four Month Before five Month Before six Month Before seven Month Before eight Month Before night Month Before ten Month Before eleven Month
---------- ---------------- ---------------- ------------------ ----------------- ----------------- ---------------- ------------------ ------------------ ------------------ ---------------- -------------------
12 3 6 0 3 0 0 0 0 0 0 0
100<count<200
200<count<500
500<count<700
count>700
0 month1,
0 month2,
0 month4,
0 month5,
0 month6,
0 month7,
0 month8,
0 month9,
0 month10,
0 month11 from dual
union
select '50-100' count,
0 month1,
0 month2,
0 month4,
0 month5,
0 month6,
0 month7,
0 month8,
0 month9,
0 month10,
0 month11 from dual
.......
最初想到的是用联合查询,但这样加上条件那些sql相当的长了