【大话IT】如何按季度半年统计 谢谢

wuxiaobo_2009

〇〇 发表于 2015-5-6 20:42
trunc((7-1)/3)+1=3

。。。。。。。。。。。。。 group by 结果呢？

xulongxc

select t2.rn,
          nvl(t1.value, 0) value
    from
    (select to_char(to_date((to_char(sysdate, 'yyyy') || month), 'yyyymm'), 'q') quarter,
           sum(value) value
      from pp
    group by to_char(to_date((to_char(sysdate, 'yyyy') || month), 'yyyymm'), 'q')) t1,
    (select level rn from dual connect by level <= 4) t2
    where t2.rn = t1.quarter(+)
  order by t2.rn;
对季度为0的结果考虑得比较全面

wagneryou

wuxiaobo_2009 发表于 2015-5-6 20:46
。。。。。。。。。。。。。 group by 结果呢？

right join (select level lv from dual connect by lv <=4) t on trunc((month-1)/3)+1 = t.lv
group by lv

xubaoqiang_it16

wagneryou 发表于 2015-5-8 16:57
right join (select level lv from dual connect by lv

验证了下楼主的sql,括号内应为select level lv from dual connect by level <=4，
除了12楼的完整的sql，我整理并验证了各位的的sql,给出另一完整sql,方便遇到同一问题的各位朋友参考！
with a as(
    select '1' month ,1 value from dual
    union all
    select '2' month ,2 value from dual
    union all
    select '4' month ,4 value from dual
    union all
    select '5' month ,3 value from dual
    union all
    select '6' month ,7 value from dual
    union all
    select '10' month ,1 value from dual
    union all
    select '12' month ,5 value from dual
)

select t.lv 季度, nvl(sum(a.value ),0) value from a
right join (select level lv from dual connect by level <=4) t
on trunc((a.month-1)/3)+1 = t.lv
group by t.lv
order by t.lv

lhqmccn

--按上半年、下半年
SELECT ceil(MONTH / 6), SUM(VALUE) FROM a GROUP BY ceil(MONTH / 6);


--按季度
SELECT ceil(MONTH / 3), SUM(VALUE) FROM a GROUP BY ceil(MONTH / 3);