查詢當年/當月/當日數(shù)據。

墨跡哥

目前需求：
1、當年的12個月的數(shù)據。如果沒有數(shù)據則為0.
2、當月的所有天數(shù)的數(shù)據。如果沒有數(shù)據則為0.（這里有三個情況，2月有的時候是28天或多少天的，還有就是30天和31天的，實在難弄就統(tǒng)計當月所有數(shù)據。）
3、當日的24小時內的數(shù)據，如果沒有數(shù)據則為0.

例子：

1. select count(*) from w3a_log_monitor_attack where attack_date like '%2013-01%' and attack_user='192.168.31.135' union all select count(*) from w3a_log_monitor_attack where attack_date like '%2013-02%' and attack_user='192.168.31.135' union all select count(*) from w3a_log_monitor_attack where attack_date like '%2013-03%' and attack_user='192.168.31.135' union all select count(*) from w3a_log_monitor_attack where attack_date like '%2013-04%' and attack_user='192.168.31.135' union all select count(*) from w3a_log_monitor_attack where attack_date like '%2013-05%' and attack_user='192.168.31.135';

復制代碼

表名：

1. w3a_log_monitor_attack

復制代碼

字段意思：

1. 
   
2. attack_date 日期字段
   
3. attack_user 用戶字段
   

復制代碼

tiankafeiwu

1. SELECT COUNT(*),CONCAT(YEAR(attack_date),'-',MONTH(attack_date),'-',DAYOFMONTH(attack_date)) FROM w3a_log_monitor_attack WHERE attack_user='192.168.31.135' and attack_date>DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY) GROUP BY CONCAT(YEAR(attack_date),'-',MONTH(attack_date),'-',DAYOFMONTH(attack_date))
   
2. 你真懶。。。。

復制代碼

回復 4# 墨跡哥


   

墨跡哥

@tiankafeiwu  來吧。。。暴風雨來的更猛烈點把。。

tiankafeiwu

1：當年所有有數(shù)據的月份（按月分組）:

1. SELECT COUNT(*),CONCAT(YEAR(attack_date),'-',MONTH(attack_date)) FROM w3a_log_monitor_attack WHERE attack_user='192.168.31.135' GROUP BY CONCAT(YEAR(attack_date),'-',MONTH(attack_date))

復制代碼

2:全年按天分組，如果要當月就加個where時間條件即可：

1. SELECT COUNT(*),CONCAT(YEAR(attack_date),'-',MONTH(attack_date),'-',DAYOFMONTH(attack_date)) FROM w3a_log_monitor_attack WHERE attack_user='192.168.31.135' GROUP BY CONCAT(YEAR(attack_date),'-',MONTH(attack_date),'-',DAYOFMONTH(attack_date))

復制代碼

3：當日24小時，即當天0點到當前時間，限當天：

1. SELECT COUNT(*),HOUR(attack_date) FROM w3a_log_monitor_attack WHERE attack_user='192.168.31.135' GROUP BY HOUR(attack_date) WHERE attack_date > DATE(NOW())

復制代碼

2和3差不多。

沒有數(shù)據的0，你用程序控制下咯

墨跡哥

回復 3# tiankafeiwu


    第一個可用。。。
    第二個要當月所有天的。。。
    第三個報錯。。。

1. mysql> SELECT COUNT(*),HOUR(attack_date) FROM w3a_log_monitor_attack WHERE attack_user='192.168.31.135' GROUP BY HOUR(attack_date) WHERE attack_date > DATE(NOW());
   
2. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE attack_date > DATE(NOW())' at line 1
   

復制代碼

wangjj20

當月：

select count(*),date_format(attack_date,'%Y-%m-%s') as day from w3a_log_monitor_attack
where w3a_log_monitor_attack >= concat(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),' 00:00:00')
group by day;


當日：

select count(*),date_format(attack_date,'%Y-%m-%s %H') as hour from w3a_log_monitor_attack
where w3a_log_monitor_attack >= concat(CURDATE(),' 00:00:00')
group by hour;

tiankafeiwu

mysql> SELECT COUNT(*),HOUR(attack_date) FROM w3a_log_monitor_attack WHERE attack_user='192.168.31.135' and attack_date > DATE(NOW()) GROUP BY HOUR(attack_date) ;
把第二個where改成and就行了，疏忽了
回復 4# 墨跡哥


   

墨跡哥

回復 5# wangjj20

1. mysql> select count(*),date_format(attack_date,'%Y-%m-%s') as day from w3a_log_monitor_attack
   
2.     -> where w3a_log_monitor_attack >= concat(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),' 00:00:00')
   
3.     -> group by day;
   
4. ERROR 1054 (42S22): Unknown column 'w3a_log_monitor_attack' in 'where clause'
   
5. mysql>
   
6. mysql>
   
7. mysql>
   
8. mysql> select count(*),date_format(attack_date,'%Y-%m-%s') as day from w3a_log_monitor_attack where w3a_log_monitor_attack >= concat(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),' 00:00:00') group by day;
   
9. ERROR 1054 (42S22): Unknown column 'w3a_log_monitor_attack' in 'where clause'
   

復制代碼

墨跡哥

回復 7# tiankafeiwu


    貌似都行了，我回頭加點數(shù)據試試。今天快下班了。。趕地鐵去。。。

1. mysql> SELECT COUNT(*),HOUR(attack_date) FROM w3a_log_monitor_attack WHERE attack_user='192.168.56.101' and attack_date > DATE(NOW()) GROUP BY HOUR(attack_date) ;
   
2. Empty set (0.01 sec)
   
3. 
   
4. mysql>
   
5. mysql>
   
6. mysql> SELECT COUNT(*),CONCAT(YEAR(attack_date),'-',MONTH(attack_date),'-',DAYOFMONTH(attack_date)) FROM w3a_log_monitor_attack WHERE attack_user='192.168.31.135' and attack_date>DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY) GROUP BY CONCAT(YEAR(attack_date),'-',MONTH(attack_date),'-',DAYOFMONTH(attack_date));
   
7. +----------+------------------------------------------------------------------------------+
   
8. | COUNT(*) | CONCAT(YEAR(attack_date),'-',MONTH(attack_date),'-',DAYOFMONTH(attack_date)) |
   
9. +----------+------------------------------------------------------------------------------+
   
10. |      170 | 2013-11-5                                                                    |
    
11. +----------+------------------------------------------------------------------------------+
    
12. 1 row in set (0.01 sec)
    

復制代碼

墨跡哥

@tiankafeiwu你看看還有沒有啥補充的，我把你的整理一下：

查當年的12個月的：

1. SELECT COUNT(*),CONCAT(YEAR(attack_date),'-',MONTH(attack_date)) FROM w3a_log_monitor_attack WHERE attack_user='192.168.31.135' GROUP BY CONCAT(YEAR(attack_date),'-',MONTH(attack_date))

復制代碼

查當月的30/31天的：

1. SELECT COUNT(*),CONCAT(YEAR(attack_date),'-',MONTH(attack_date),'-',DAYOFMONTH(attack_date)) FROM w3a_log_monitor_attack WHERE attack_user='192.168.31.135' and attack_date>DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY) GROUP BY CONCAT(YEAR(attack_date),'-',MONTH(attack_date),'-',DAYOFMONTH(attack_date))

復制代碼

查小時的（24小時）:

1. SELECT COUNT(*),HOUR(attack_date) FROM w3a_log_monitor_attack WHERE attack_user='192.168.31.135' and attack_date > DATE(NOW()) GROUP BY HOUR(attack_date) ;

復制代碼