问一个sql查询语句,高手帮忙一下,谢谢!

Question

select 开点,到点,trunc(decode(sign(到点-to_date('2005-06-01','yyyy-mm-dd')),1,to_date('2005-06-01','yyyy-mm-dd'),到点)-decode(sign(开点-to_date('2005-01-01','yyyy-mm-dd')),1,开点,to_date('2005-01-01','yyyy-mm-dd'))) 到点开点
from tbname

Answer 1

select table.*,decode(to_char(date_from,'MM')||to_char(date_to,'MM'),
'0405',trunc(date_to)-trunc(last_day(date_from)),
'0505',trunc(date_to)-trunc(date_from),
'0506',trunc(last_day(date_from))-trunc(date_from)) num 
from table
where to_char(date_from,'YYYYMM') = '200505' or to_char(date_to,'YYYYMM') = '200505'不是很严密，如果存在2005-03-30 12:23:33   2005-05-05 12:23:33 这样的记录的话还需要修改  
2005-04-30 12:23:33   2005-05-05 12:23:33   //这条应该是5 天
这条算得是2005-05-05 到2005-04-30之间de天数，如果算到2005-05-01就只有4天

Answer 2

SQL> desc test
Name       Type         Nullable Default Comments 
---------- ------------ -------- ------- -------- 
START_TIME VARCHAR2(14) Y                         
END_TIME   VARCHAR2(14) Y                         SQL> select * from test;START_TIME     END_TIME
-------------- --------------
20050430122333 20050505122333
20050501122333 20050507122333
20050530122333 20050606122333SQL> 
SQL>     select start_time,end_time,
  2      (case when substr(end_time,5,2)='06' and substr(start_time,5,2)='05'
  3               then trunc(last_day(to_date(start_time,'yyyymmddhh24miss')))-trunc(to_date(start_time,'yyyymmddhh24miss'))
  4            when substr(end_time,5,2)='05' and substr(start_time,5,2)='05'
  5               then trunc(to_date(end_time,'yyyymmddhh24miss'))-trunc(to_date(start_time,'yyyymmddhh24miss'))
  6            when substr(end_time,5,2)='05' and substr(start_time,5,2)<'05'
  7               then trunc(to_date(end_time,'yyyymmddhh24miss'))-trunc(last_day(to_date('20050401','yyyymmdd')))
  8               end ) as tt from test;START_TIME     END_TIME               TT
-------------- -------------- ----------
20050430122333 20050505122333          5
20050501122333 20050507122333          6
20050530122333 20050606122333          1

Answer 3

补充：
    select start_time,end_time,
    (case when substr(end_time,5,2)>'05' and substr(start_time,5,2)='05'
             then trunc(last_day(to_date(start_time,'yyyymmddhh24miss')))-trunc(to_date(start_time,'yyyymmddhh24miss'))
          when substr(end_time,5,2)='05' and substr(start_time,5,2)='05'
             then trunc(to_date(end_time,'yyyymmddhh24miss'))-trunc(to_date(start_time,'yyyymmddhh24miss')) 
          when substr(end_time,5,2)='05' and substr(start_time,5,2)<'05'
             then trunc(to_date(end_time,'yyyymmddhh24miss'))-trunc(last_day(to_date('20050401','yyyymmdd')))   
             end ) as tt from test;
如果是date类型可以用to_char(start_time,'d') 得到月份