oracle 日期相关的东东和几个应用的例子

获取本月第一天和最后一天

select to_char(trunc(add_months(last_day(sysdate), -1) + 1), 'yyyy-mm-dd') "本月第一天",
       to_char(last_day(sysdate), 'yyyy-mm-dd') "本月最后一天"
from dual

 

一个综合运用IN 筛选的 例子

select t.firm_id, t.customer_id, t.commodity_id, t.bs_flag, t.qty from ce.trd_oitrades t
   where t.customer_id in (select c.customer_id from  ce.bas_customerinfo c where c.type='N') and
         t.commodity_id in (select f.commodity_id from ce.bas_f_commodityinfo f where trunc(add_months(last_day(sysdate), -1) + 1)<f.l_tra_date and f.l_tra_date<last_day(sysdate)) ;

 

去除customerid列重复，并合并 HOLD_VOL列

select distinct t.customerid, sum(t.hold_vol) from es_bo_hold_indelmon t
group by t.customerid
order by t.customerid