定义一个计算上个月的函数 create or replace function F_Pre_month(pi_yearmonth in varchar2) return varchar2 is Result varchar2(6); year number; month number; begin year:=to_number(pi_yearmonth,1,4); month:=to_number(pi_yearmonth,5,2); month:=month-1; if(month<=0) then month:=12; year:=year-1; end if; Result:=year||lpad(to_char(month),2,'0'); return(Result); end F_Pre_month;然后select 2012||lpad(to_char(rownum),2,'0') 月份, F_Pre_month(2012||lpad(to_char(rownum),2,'0'))||'20' 起始日期, 2012||lpad(to_char(rownum),2,'0')||'21' 结束日期 from dual connect by rownum<=12 可以算出每个月的起始和结束日期 其他的就好处理了
不行的话 只能麻烦的分12次获取了 select b.unit_name, sum(case when case_date between date'2010-12-20' and date'2011-01-20' then foot_num else 0 end) 一,sum(case when case_date between date'2011-01-20' and date'2011-02-20' then foot_num else 0 end) 二,... from case_tb a,unit_tb b where a.unit_code = b.unit_code group by b.unit_name
select code,case monthNum when 1 then total else 0 end as month1, case monthNum when 2 then total else 0 end as month2, case monthNum when 3 then total else 0 end as month3, case monthNum when 4 then total else 0 end as month4, case monthNum when 5 then total else 0 end as month5, case monthNum when 6 then total else 0 end as month6, case monthNum when 7 then total else 0 end as month7, case monthNum when 8 then total else 0 end as month8, case monthNum when 9 then total else 0 end as month9, case monthNum when 10 then total else 0 end as month10, case monthNum when 11 then total else 0 end as month11, case monthNum when 12 then total else 0 end as month12 from ( select code,case when to_char(inputDate,'dd')>20 then to_char(inputDate,'mm') + 1 else to_char(inputDate,'mm') as monthNum ,sum(num) as total from tableName )
最外层还要加一个SUM和与名称表的关联
不推荐枚举 反正你的切割点就是每月的20号嘛select t.createddate, to_date(to_char(t.createddate, 'yyyymm') || '20', 'yyyy-mm-dd'), case when trunc(t.createddate) > to_date(to_char(t.createddate, 'yyyymm') || '20', 'yyyy-mm-dd') then to_char(add_months(trunc(t.createddate), 1), 'yyyymm') else to_char(trunc(t.createddate), 'yyyymm') end as flag from tbo_002 t 一个例子 对数据打上flag
SELECT add_months(ADD_MONTHS(DATE'2010-12-01',LEVEL)+19,-1) AS COL1
FROM DUAL
CONNECT BY LEVEL <= 12 COL1
--------------------------
1 2010/12/20
2 2011/1/20
3 2011/2/20
4 2011/3/20
5 2011/4/20
6 2011/5/20
7 2011/6/20
8 2011/7/20
9 2011/8/20
10 2011/9/20
11 2011/10/20
12 2011/11/20
定义一个计算上个月的函数
create or replace function F_Pre_month(pi_yearmonth in varchar2) return varchar2 is
Result varchar2(6);
year number;
month number;
begin
year:=to_number(pi_yearmonth,1,4); month:=to_number(pi_yearmonth,5,2);
month:=month-1;
if(month<=0) then
month:=12;
year:=year-1;
end if;
Result:=year||lpad(to_char(month),2,'0');
return(Result);
end F_Pre_month;然后select
2012||lpad(to_char(rownum),2,'0') 月份,
F_Pre_month(2012||lpad(to_char(rownum),2,'0'))||'20' 起始日期,
2012||lpad(to_char(rownum),2,'0')||'21' 结束日期
from dual connect by rownum<=12
可以算出每个月的起始和结束日期
其他的就好处理了
select b.unit_name,
sum(case when case_date between date'2010-12-20' and date'2011-01-20' then foot_num else 0 end) 一,sum(case when case_date between date'2011-01-20' and date'2011-02-20' then foot_num else 0 end) 二,...
from case_tb a,unit_tb b
where a.unit_code = b.unit_code
group by b.unit_name
case monthNum when 2 then total else 0 end as month2,
case monthNum when 3 then total else 0 end as month3,
case monthNum when 4 then total else 0 end as month4,
case monthNum when 5 then total else 0 end as month5,
case monthNum when 6 then total else 0 end as month6,
case monthNum when 7 then total else 0 end as month7,
case monthNum when 8 then total else 0 end as month8,
case monthNum when 9 then total else 0 end as month9,
case monthNum when 10 then total else 0 end as month10,
case monthNum when 11 then total else 0 end as month11,
case monthNum when 12 then total else 0 end as month12
from (
select code,case when to_char(inputDate,'dd')>20 then to_char(inputDate,'mm') + 1 else to_char(inputDate,'mm') as monthNum ,sum(num) as total from tableName
)
to_date(to_char(t.createddate, 'yyyymm') || '20', 'yyyy-mm-dd'),
case
when trunc(t.createddate) >
to_date(to_char(t.createddate, 'yyyymm') || '20', 'yyyy-mm-dd') then
to_char(add_months(trunc(t.createddate), 1), 'yyyymm')
else
to_char(trunc(t.createddate), 'yyyymm')
end as flag
from tbo_002 t
一个例子 对数据打上flag