选择所有MySQL数据，按月份、年份统计不正确的记录 - Select all MySQL data, count records by month, year not working correctly

Select all MySQL data, count records by month, year not working correctly

我有一个数据库表，其中有一个IP地址列表，每个IP地址都有一个datetime列，基本上类似于以下示例：

ip_address    | date_first_viewed
192.122.42.2    2015-12-23 14:03:10
183.123.13.4    2015-12-23 02:11:10
193.311.31.6    2015-11-10 12:29:00

我正试图根据月份、年份来选择、计数和分组数据，这样它就会显示为：

(2015, 12)  2
(2015, 11)  1

这是我当前使用的查询，尽管它没有返回任何日期？

$sql8 = "SELECT COUNT(ip_address) as count, EXTRACT(YEAR_MONTH FROM date_first_viewed) as date
         FROM `all_website_stats`
         GROUP BY EXTRACT(YEAR_MONTH FROM date_first_viewed)";

您的SQL看起来不错。只需使用backticks转义保留字count和date即可避免问题。要获得所需格式的日期，您可以尝试此查询，它只连接您想要格式化的其他字符：

SELECT CONCAT('(', YEAR(date_first_viewed), ', ', MONTH(date_first_viewed), ')') FROM table;

举个例子：

SELECT COUNT(ip_address) as `count`, CONCAT('(', YEAR(date_first_viewed), ', ', MONTH(date_first_viewed), ')') as `date`
     FROM `all_website_stats` GROUP BY CONCAT('(', YEAR(date_first_viewed), ', ', MONTH(date_first_viewed), ')')";

应该这么做！