sql 语句

<pre name="code" class="sql">查询一段时间内数量大于一定数的最新时间

<span style="font-family: Arial, Helvetica, sans-serif;">select max(datetime) from table    where 区间  having count(0) > 数量</span>

按每日统计数据

select to_char(datetime,'mm-dd') , count(0)as counts from  table t  where  substr(to_char(datetime,' HH24:MI:SS'),4,6) =':00:00'  and 取值区间 group by to_char(datetime,'mm-dd') order by datetime

按每月统计数据

select to_char(datetime,'yyyy-mm') , count(0)as counts from  table t  where  substr(to_char(datetime,' HH24:MI:SS'),4,6) =':00:00'  and 取值区间 group by to_char(datetime,'yyyy-mm') order by datetime

从左边对字符串使用指定的字符进行填充

select lpad(字段,长度,'填充字符') from dual;

数据库中数据每分钟条数时，按五分钟统计

select
  FLOOR((datetime-to_date(sysdate, 'dd-mm-yyyy hh24:mi:ss'))*24*60/5),count(0)
 from table t
where 
(datetime >= to_date(<span style="font-family: Arial, Helvetica, sans-serif;">sysdate-1</span><span style="font-family: Arial, Helvetica, sans-serif;">, 'dd-mm-yyyy hh24:mi:ss')</span>
and datetime <= to_date(<span style="font-family: Arial, Helvetica, sans-serif;">sysdate</span><span style="font-family: Arial, Helvetica, sans-serif;">, 'dd-mm-yyyy hh24:mi:ss'))</span>
group by   FLOOR((datetime-to_date(sysdate, 'dd-mm-yyyy hh24:mi:ss'))*24*60/5)
order by   FLOOR((datetime-to_date(sysdate, 'dd-mm-yyyy hh24:mi:ss'))*24*60/5)

查看某用户历史操作记录

     select *
  from V_$SQL t
 WHERE  t.PARSING_SCHEMA_NAME = '用户名'
   AND T.SQL_TEXT LIKE 'delete %'
 order by t.LAST_ACTIVE_TIME

使用存储过程，删除历史重复数据(图方便就多命名了变量i，实际应该直接使用while p_para1 < timeEnd来遍历吧)

create or replace procedure proc1(
  p_para1 date 
)as

begin
  delete from table 
  where col in (select  col
  from table where col2= p_para1  
  group  by  col having  count(col) > 1) 
  and rowid  in (select min(rowid) 
  from  table where col2= p_para1 
  group by col having count(col)>1);
end;

DECLARE
             p_para1 date;
             i int:=1;
       BEGIN
             p_para1 := to_date('2015-02-01 02:25:00','yyyy-mm-dd hh24:mi:ss');
             while i<=3000 loop
                   proc1(p_para1 => p_para1);
                   p_para1 :=p_para1+5/(24*60);
                   i:=i+1;
             END loop;
       END;