- 论坛徽章:
- 3
|
本帖最后由 墨迹哥 于 2013-11-27 14:41 编辑
自己琢磨出来了:
结果是这样的:- mysql> select month,sum(count_all) from (SELECT CONCAT(YEAR(time_start),'-',MONTH(time_start)) month,count(*) count_all FROM w3a_net_monitor_web_attack GROUP BY CONCAT(YEAR(time_start),'-',MONTH(time_start)) union all SELECT CONCAT(YEAR(time_start),'-',MONTH(time_start)) month,count(*) count_all FROM w3a_net_monitor_network_attack GROUP BY CONCAT(YEAR(time_start),'-',MONTH(time_start))) t group by month;
- +---------+----------------+
- | month | sum(count_all) |
- +---------+----------------+
- | 2013-10 | 1 |
- | 2013-11 | 1593 |
- | 2013-8 | 1 |
- | 2013-9 | 1 |
- +---------+----------------+
- 4 rows in set (0.01 sec)
复制代码 具体是这样的,之前论坛高人教了一招查询的方法。group by .
后来自己摸索。具体有个问题不太理解。
以下是并和两张表的查询结果,查出来是正常的。- mysql> SELECT CONCAT(YEAR(time_start),'-',MONTH(time_start)) month,count(*) count_all FROM w3a_net_monitor_web_attack GROUP BY CONCAT(YEAR(time_start),'-',MONTH(time_start)) union all SELECT CONCAT(YEAR(time_start),'-',MONTH(time_start)) month,count(*) count_all FROM w3a_net_monitor_network_attack GROUP BY CONCAT(YEAR(time_start),'-',MONTH(time_start));
- +---------+-----------+
- | month | count_all |
- +---------+-----------+
- | 2013-10 | 1 |
- | 2013-11 | 826 |
- | 2013-8 | 1 |
- | 2013-9 | 1 |
- | 2013-11 | 767 |
- +---------+-----------+
- 5 rows in set (0.00 sec)
复制代码 但是当我要进行Group by 继续下去的时候,就不行了。。还请请教下这是为什么?- mysql> select CONCAT(YEAR(time_start),'-',MONTH(time_start)),count(*) from (SELECT CONCAT(YEAR(time_start),'-',MONTH(time_start)) month,count(*) count_all FROM w3a_net_monitor_web_attack GROUP BY CONCAT(YEAR(time_start),'-',MONTH(time_start)) union all SELECT CONCAT(YEAR(time_start),'-',MONTH(time_start)) month,count(*) count_all FROM w3a_net_monitor_network_attack GROUP BY CONCAT(YEAR(time_start),'-',MONTH(time_start))) b group month;
- 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 'month' at line 1
复制代码 还有是这样的。。- mysql> select CONCAT(YEAR(month),'-',MONTH(month)) aaa,count(*) from (SELECT CONCAT(YEAR(time_start),'-',MONTH(time_start)) month,count(*) count_all FROM w3a_net_monitor_web_attack GROUP BY CONCAT(YEAR(time_start),'-',MONTH(time_start)) union all SELECT CONCAT(YEAR(time_start),'-',MONTH(time_start)) month,count(*) count_all FROM w3a_net_monitor_network_attack GROUP BY CONCAT(YEAR(time_start),'-',MONTH(time_start))) b group by aaa;
- +------+----------+
- | aaa | count(*) |
- +------+----------+
- | NULL | 5 |
- +------+----------+
- 1 row in set, 6 warnings (0.02 sec)
复制代码 |
|