日期是date类型:select to_char(tdate-24,'yyyymm') cus_mon,sum(tmoney) from table group by to_char(tdate-24,'yyyymm'); 日期是varchar2类型:select to_char(to_date(tdate,'yyyy-mm-dd')-24,'yyyymm') cus_mon,sum(tmoney) from table group by to_char(to_date(tdate,'yyyy-mm-dd')-24,'yyyymm');
应该不会吧 测试下 create table t1 (c1 date,c2 number(10,2)); insert into t1 values (date'2012-02-01',100.55); insert into t1 values (date'2012-03-11',200); insert into t1 values (date'2012-03-25',333); insert into t1 values (date'2012-04-01',111); insert into t1 values (date'2012-04-24',555); insert into t1 values (date'2012-04-26',321); insert into t1 values (date'2012-05-01',222.22); insert into t1 values (date'2012-05-29',155.15); insert into t1 values (date'2012-06-11',234); commit;select to_char(c1-24,'yyyymm') c1,sum(c2) c2 from t1 group by to_char(c1-24,'yyyymm') order by to_char(c1-24,'yyyymm') c1 c2 -------------------------------- 1 201201 100.55 2 201202 200 3 201203 999 4 201204 543.22 5 201205 389.15 可以看到3月25到4月24为一组 合计999 4月26和5月1号为一组
应该不会吧 测试下
create table t1 (c1 date,c2 number(10,2));
insert into t1 values (date'2012-02-01',100.55);
insert into t1 values (date'2012-03-11',200);
insert into t1 values (date'2012-03-25',333);
insert into t1 values (date'2012-04-01',111);
insert into t1 values (date'2012-04-24',555);
insert into t1 values (date'2012-04-26',321);
insert into t1 values (date'2012-05-01',222.22);
insert into t1 values (date'2012-05-29',155.15);
insert into t1 values (date'2012-06-11',234);
commit;select to_char(c1-24,'yyyymm') c1,sum(c2) c2
from t1
group by to_char(c1-24,'yyyymm')
order by to_char(c1-24,'yyyymm') c1 c2
--------------------------------
1 201201 100.55
2 201202 200
3 201203 999
4 201204 543.22
5 201205 389.15
可以看到3月25到4月24为一组 合计999 4月26和5月1号为一组
非常感谢两位的回复!
好学若饥!
select to_char(add_months(c1-24,1),'yyyymm') c1,sum(c2) c2
from t1
group by to_char(add_months(c1-24,1),'yyyymm')
order by c1 c1 c2
--------------------------
1 201202 100.55
2 201203 200
3 201204 999
4 201205 543.22
5 201206 389.15