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 twhere (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 )asbegin  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;