查詢當(dāng)年/當(dāng)月/當(dāng)日數(shù)據(jù)。

墨跡哥

目前需求：
1、當(dāng)年的12個(gè)月的數(shù)據(jù)。如果沒(méi)有數(shù)據(jù)則為0.
2、當(dāng)月的所有天數(shù)的數(shù)據(jù)。如果沒(méi)有數(shù)據(jù)則為0.（這里有三個(gè)情況，2月有的時(shí)候是28天或多少天的，還有就是30天和31天的，實(shí)在難弄就統(tǒng)計(jì)當(dāng)月所有數(shù)據(jù)。）
3、當(dāng)日的24小時(shí)內(nèi)的數(shù)據(jù)，如果沒(méi)有數(shù)據(jù)則為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';

復(fù)制代碼

表名：

1. w3a_log_monitor_attack

復(fù)制代碼

字段意思：

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

復(fù)制代碼

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. 你真懶。。。。

復(fù)制代碼

回復(fù) 4# 墨跡哥


   

墨跡哥

@tiankafeiwu  來(lái)吧。。。暴風(fēng)雨來(lái)的更猛烈點(diǎn)把。。

tiankafeiwu

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

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))

復(fù)制代碼

2:全年按天分組，如果要當(dāng)月就加個(gè)where時(shí)間條件即可：

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))

復(fù)制代碼

3：當(dāng)日24小時(shí)，即當(dāng)天0點(diǎn)到當(dāng)前時(shí)間，限當(dāng)天：

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())

復(fù)制代碼

2和3差不多。

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

墨跡哥

回復(fù) 3# tiankafeiwu


    第一個(gè)可用。。。
    第二個(gè)要當(dāng)月所有天的。。。
    第三個(gè)報(bào)錯(cuò)。。。

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
   

復(fù)制代碼

wangjj20

當(dāng)月：

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;


當(dāng)日：

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) ;
把第二個(gè)where改成and就行了，疏忽了
回復(fù) 4# 墨跡哥


   

墨跡哥

回復(fù) 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'
   

復(fù)制代碼

墨跡哥

回復(fù) 7# tiankafeiwu


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

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)
    

復(fù)制代碼

墨跡哥

@tiankafeiwu你看看還有沒(méi)有啥補(bǔ)充的，我把你的整理一下：

查當(dāng)年的12個(gè)月的：

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))

復(fù)制代碼

查當(dāng)月的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))

復(fù)制代碼

查小時(shí)的（24小時(shí)）:

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) ;

復(fù)制代碼