Common SQL statements

Time:

SELECT * from Mread.sup_operatelog a where a.stepid = ' 040110 ' and A.operatetime > To_date (' 20160525 13:09 ', ' yyyymm DD Hh24:mi ') and A.operatetime < To_date (' 20160525 13:15 ', ' yyyymmdd hh24:mi ') Order BY operatetime Desc

InStr (Sourcestring,deststring,start,appearposition)

InStr (' Source string ', ' target string ', ' Start position ', ' first occurrence ')

substr (String, start_position, [length])

substr (' Target string ', start position, length)

REPLACE (STR,FROM_STR,TO_STR)

———— To_str does not write to indicate NULL, NULL

Example

Select replace (substr (a.operateobject,                       1,        &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;INSTR ( a.operateobject,  ' | ',  1, 1)  - 1),                 ' book ID: ')  bookid,        Replace (substr (a.operateobject,                       instr (a.operateobject,  ' | ',  1, 4),                        instr (a.operateobject,  ' | ',  1, 5)  -             &nbsP;         instr (a.operateobject,  ' | ',  1, 4)   - 1),                ' | Chapter ID: ')  mcpchapterid,       replace (substr (a.operateobject,                        instr (a.operateobject,  ' | ',  1, 6),                       instr (a.operateobject,  ' | ', &NBSP;1,&NBSP;7)  -                       instr (a.operateobject,  ' | ',  1, 6)  - 1 ),                ' | Request time: ')   Reqtime  from mread.sup_operatelog a where a.stepid =  ' 040110 '    and  A.operatetime > to_date (' 20160520 3:09 ',  ' Yyyymmdd hh24:mi ')    and  a.operatetime < to_date (' 20160520 13:15 ',  ' Yyyymmdd hh24:mi ')     and substr (a.operateobject,               instr (a.operateobject,  ' | ',  1, 10)  + 1,               instr (a.operateobject,  ' | ',  1, 11)  -               instr (A.operateobject,   ' | ',  1, 10)  - 1)  =  ' processing result return code: 0 '

Case

select case         when a.operatetime <  To_date (' 20160525 13:10 ',  ' Yyyymmdd hh24:mi ')  then1          else2       end,        count (*)   from mread.sup_operatelog a where a.operatetime >  To_date (' 20160525 13:09 ',  ' Yyyymmdd hh24:mi ')    and a.operatetime <  to_date (' 20160525 13:15 ',  ' Yyyymmdd hh24:mi ')  group by case             when a.operatetime <                  to_date (' 20160525  13:10 ',  ' Yyyymmdd hh24:mi ')  then1             else2          end                                select case         when op_date  <= 60 then1         when op_date  > 60 and op_date <= 120 then2          when op_date > 120 and op_date <= 600 then3          else4       end,        count (*)   from cfgbk.gc0524_sup_operatelog group  by case            when op_date  <= 60 then1            when op_date > 60 and  op_date <= 120 then2             when op_date > 120 and op_date <= 600 then3             else4           end;

Time Difference Conversion:

Select Trunc (sysdate, ' mi '), A.implicitcreatetime, (Trunc (sysdate, ' mi ')-a.implicitcreatetime) * 60 * 60 * 2 4 op_date from Cntdb. Con_t_basechapter a WHERE a.implicitcreatetime > to_date (' 20160526 16:09 ', ' yyyymmdd hh24:mi ') and A.implicitcreatetime < To_date (' 20160526 16:15 ', ' yyyymmdd hh24:mi ')

This article is from the "Seeworld" blog, make sure to keep this source http://depops2016.blog.51cto.com/4205997/1783776

Common SQL statements