隔月分組查詢:
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號(hào)频祝,思想將上月的數(shù)據(jù)也歸并到本月
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)
查詢結(jié)果分支語句:
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;
日期之間天數(shù):
select trunc(sysdate)-trunc(TO_DATE('2019-08-24','YYYY-MM-DD')) from dual;