数据库查询语句

按错误信息和日期分组查询

    select  trunc(s.create_date ),s.result_desc,count(*) from EUC_LOG_LOGIN s where s.id in(Select min(id) FROM EUC_LOG_LOGIN m   
    where  m.create_date < to_date('2012-08-29 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-08-26 00:00:00','yyyy-mm-dd hh24:mi:ss')<m.create_date group by   
    (m.esuid,m.user_name,m.login_result,m.result_desc,trunc(m.create_date)))    
    and s.create_date < to_date('2012-08-29 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-08-26 00:00:00','yyyy-mm-dd hh24:mi:ss')<s.create_date    
     group by trunc(s.create_date ),s.result_desc order by trunc(s.create_date )

按天统计成功登录的人次

    select trunc(t.create_date ) date_time,count( trunc(t.create_date )) count  
    from EUC_LOG_LOGIN t  
     where t.create_date < to_date('2012-08-27 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-07-19 00:00:00','yyyy-mm-dd hh24:mi:ss')<t.create_date  
    and t.login_result = '100'
    group by trunc(t.create_date ) order by date_time;  

按天统计成功登录的人数

select trunc(t.create_date ) date_time,count( t.create_date) count
    from
    (    select  * from EUC_LOG_LOGIN s where s.id in(Select min(id) FROM EUC_LOG_LOGIN m 
    where m.create_date < to_date('2012-08-28 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-08-26 00:00:00','yyyy-mm-dd hh24:mi:ss')<m.create_date group by 
    (m.user_name,m.login_result) ) )t
    where t.create_date < to_date('2012-08-28 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-08-26 00:00:00','yyyy-mm-dd hh24:mi:ss')<t.create_date  
    and t.login_result = '200'
    group by trunc(t.create_date ) order by date_time;


        select trunc(t.create_date ) date_time,count( t.create_date) count  
    from
    (select  * from EUC_LOG_LOGIN s where s.id in(Select min(id) FROM EUC_LOG_LOGIN m group by 
    (m.user_name,m.login_result))) t
    where t.create_date < to_date('2012-08-27 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-07-19 00:00:00','yyyy-mm-dd hh24:mi:ss')<t.create_date  
    and t.login_result = '100'
    group by trunc(t.create_date ) order by date_time;

按天统计成功注册的次数

    select trunc(t.create_date ) date_time,count( trunc(t.create_date )) count  
    from EUC_LOG_REGISTER t  
     where t.create_date < to_date('2012-08-27 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-07-19 00:00:00','yyyy-mm-dd hh24:mi:ss')<t.create_date  
    and t.register_result = '100'
    group by trunc(t.create_date ) order by date_time; 

查某段时间的数据，并按日分组：

select trunc(t.register_time ) date_time,count( trunc(t.register_time )) count
from EUC_USER t
 where t.register_time < to_date('2012-07-27 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-07-19 00:00:00','yyyy-mm-dd hh24:mi:ss')<t.register_time
group by trunc(t.register_time );

查询昨天的数据：

select *from EUC_LOG_ST t WHERE  t.create_date> trunc(SYSDATE-1)+ 8/24 
and t.create_date < trunc(SYSDATE) + 8/24