oracle 隔月分组查询，查询结果分支语句，月份去零，日期之间

2019-08-15 本文已影响0人铁皮农夫

隔月分组查询：

create table t3 (c1 varchar(22),c2 number(10,2));

insert into t3 values ('2012-02-01',100.55);

insert into t3 values ('2012-03-11',200);

insert into t3 values ('2012-03-21',333);

insert into t3 values ('2012-04-01',111);

insert into t3 values ('2012-04-20',555);

insert into t3 values ('2012-04-22',321);

insert into t3 values ('2012-05-01',222.22);

insert into t3 values ('2012-05-29',155.15);

insert into t3 values ('2012-06-11',234);

commit;

查询上月21到本月20号，思想将上月的数据也归并到本月

select substr(to_char(add_months((to_date(c1,'yyyy-mm-dd')-20),1),'yyyy-mm-dd'),1,7) ,sum(c2) c2

from t3

group by substr(to_char(add_months((to_date(c1,'yyyy-mm-dd')-20),1),'yyyy-mm-dd'),1,7)

order by substr(to_char(add_months((to_date(c1,'yyyy-mm-dd')-20),1),'yyyy-mm-dd'),1,7)

查询结果分支语句：

select (case

when to_date('2008-05-01', 'yyyy-MM-dd') >=

to_date('2008-03-01', 'yyyy-MM-dd') then

to_date('2008-05-01', 'yyyy-MM-dd')

else

to_date('2008-03-01', 'yyyy-MM-dd')

end) test

from dual

月份去零：

select ltrim(to_char(to_date('2008-01-01', 'yyyy-MM-dd'),'mm'),'0') from dual;

select to_number(to_char(to_date('2008-01-01', 'yyyy-MM-dd'),'mm')) from dual;

select to_char(to_date('2008-01-01', 'yyyy-MM-dd'),'fmmm') from dual;

日期之间天数：

select trunc(sysdate)-trunc(TO_DATE('2019-08-24','YYYY-MM-DD')) from dual;