oracle分时间段查询SQL

如我有以下一个表，
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的和
格式像下面这样
本月  上一个月上两个月 ....  ....   ....

11 个解决方案

#1

最好能用oracle的分析函数,谢谢了

#2

select sum(a.count) 本月,sum(decode(to_char(ddate,'yyyymm'),to_char(add_months(ddate,-1),'yyyymm'),count,0) 上月,sum(decode(to_char(ddate,'yyyymm'),to_char(add_months(ddate,-2),'yyyymm'),count,0) 前两月,sum(decode(to_char(ddate,'yyyymm'),to_char(add_months(ddate,-3),'yyyymm'),count,0) 前三月,...,sum(decode(to_char(ddate,'yyyymm'),to_char(add_months(ddate,-11),'yyyymm'),count,0) 前十一月
from test a
where to_char(ddate,'yyyymm') ='200709'
group a.id,to_char(ddate,'yyyymm')

#3

楼上的方法我也试过，也可以，我是想找一种简单一点的方法，不知道oracle有没有支持此类统计的分析函数

#4

试试看~~~

select max(decode(zz.ddate,to_char(to_date('20070701','yyyymmdd'),'yyyymm'),zz.amount,0)) as "This Month",
        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

#5

用add_months　和　to_char就可以了..

#6

路过

#7

同学习，高手好多~

#8

我要查询如下所示，总不能把时间都列出来吧

                   本月       上一月     上两月   .....上十一个月

count>50           记录数

50<count<100
100<count<200
200<count<500
500<count<700
count>700

#9

select '小于50' count,
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相当的长了

#10

XD,能否把数据对应的结果贴出来？不是很明白的你的count>50等条件 ...

#11

哦，写错了，是count<50，就是按照count的大小进行分段,求出每个段的记录数

#1