mysql - SQL Group By of Date 不起作用

Question

我有这个 SQL 代码:

SELECT EXTRACT(MONTH FROM timestamp) AS StatMonth, timestamp
  FROM server_stats
 GROUP BY StatMonth
 ORDER BY StatMonth DESC;

架构:

CREATE TABLE IF NOT EXISTS `server_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` int(11) NOT NULL,
  `players_online` bigint(20) NOT NULL,
  `active_players` bigint(20) NOT NULL,
  `total_copper` bigint(20) NOT NULL,
  `total_items` bigint(20) NOT NULL,
  `total_item_weight` bigint(20) NOT NULL,
  `total_houses` bigint(20) NOT NULL,
  `total_boats` bigint(20) NOT NULL,
  `total_pets` bigint(20) NOT NULL,
  `total_experience` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=63 ;

示例数据:

INSERT INTO `server_stats` (`id`, `timestamp`, `players_online`, `active_players`,
        `total_copper`, `total_items`, `total_item_weight`, `total_houses`,
        `total_boats`, `total_pets`, `total_experience`) VALUES
(53, 1317288600, 227, 718, 155037828, 68231283, 25169498, 14822, 2112, 683, 7776385802),
(54, 1317290400, 224, 718, 155038479, 68233576, 25163526, 14822, 2112, 683, 7776669410),
(55, 1317292200, 215, 718, 155100860, 68239613, 25160397, 14822, 2112, 683, 7776933725),
(56, 1317294000, 201, 717, 155095895, 68240842, 25159809, 14822, 2112, 683, 7777196134),
(57, 1317295800, 204, 718, 155090280, 68248539, 25170275, 14822, 2112, 683, 7777419238),
(58, 1317297600, 194, 718, 155105906, 68259213, 25163212, 14823, 2112, 683, 7777657681),
(59, 1317682800, 230, 724, 157323143, 68731571, 25185326, 14908, 2119, 692, 7834358253),
(60, 1317684600, 213, 724, 157323178, 68733325, 25186412, 14908, 2119, 692, 7834637558),
(61, 1317686400, 219, 724, 157314147, 68737824, 25197057, 14908, 2119, 692, 7834904982),
(62, 1317688200, 225, 725, 157329100, 68736384, 25190554, 14909, 2119, 692, 7835169244);

我正在使用上面的 SELECT 语句来显示每个月。在这种情况下应该是九月 (9) 和十月 (10)。但是，它只显示九月，而不显示十月。

有人能给我解释一下吗？

Answer 1

这在 MySQL 5.1 中工作正常，前提是您使用 MONTH FROM from_unixtime(timestamp)。