id  date_log

1   2012-08-10 13:39:56

2   2012-08-10 13:39:57

3   2012-08-10 13:39:58

4   2012-09-10 13:39:56

5   2012-09-10 13:39:56

6   2012-09-11 13:39:56

7   2012-09-11 13:39:57

8   2012-09-11 13:39:57

9   2012-12-01 12:00:00

10  2012-12-01 13:00:00

select count(date_log) 当天 from t_monitor_log where trunc(date_log)=trunc(sysdate)

select count(date_log) 当月 from monitor_log where add_months(date_log, 1)>sysdate

select count(date_log) 当前季度 from t_monitor_log where add_months(date_log, 3)>sysdate

------解决方案--------------------
SQL code

select count(*) 当天数量 from 你的表 where trunc(date_log)=trunc(sysdate);
select count(*) 当月数量 from 你的表 where to_char(date_log,'yyyymm')=to_char(sysdate,'yyyymm');
select count(*) 当前季度 from 你的表 where to_char(date_log,'yyyy')=to_char(sysdate,'yyyy') and trunc((to_char(date_log,'mm')+2)/3)=trunc((to_char(sysdate,'mm')+2)/3);

------解决方案--------------------
SQL code
select t1.*, t2.*, t3.* from 
(select count(date_log) 当天 from t_monitor_log where trunc(date_log)=trunc(sysdate))t1,
(select count(date_log) 当月 from t_monitor_log where add_months(date_log, 1)>sysdate)t2,
(select count(date_log) 当前季度 from t_monitor_log where add_months(date_log, 3)>sysdate)t3

------解决方案--------------------
SQL code

--扫描次数太多鸟。用这个
SELECT COUNT(CASE
               WHEN TRUNC(DATE_LOG) = TRUNC(SYSDATE) THEN
                1
               ELSE
                NULL
             END) 当天,
       COUNT(CASE
               WHEN ADD_MONTHS(DATE_LOG, 1) > SYSDATE THEN
                1
               ELSE
                NULL
             END) 当月,
       COUNT(CASE
               WHEN ADD_MONTHS(DATE_LOG, 3) > SYSDATE THEN
                1
               ELSE
                0
             END) 当前季度
  FROM T_MONITOR_LOG