表 t_monitor_log
- SQL code
id date_log1 2012-08-10 13:39:562 2012-08-10 13:39:573 2012-08-10 13:39:584 2012-09-10 13:39:565 2012-09-10 13:39:566 2012-09-11 13:39:567 2012-09-11 13:39:578 2012-09-11 13:39:579 2012-12-01 12:00:0010 2012-12-01 13:00:00
查询 当天的个数， 当月的个数，当前季度的个数。
比如当前时间为2012-09-11日，则显示结果如下：
当天： 3个
当月： 5个
当前季度：8个
------解决方案--------------------
- SQL code
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)>sysdateselect 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