mysql查询从1自动计数_MySQL查询每月获取计数

我有一个供用户使用的表，该表具有一个名为“ created”的字段，该字段具有注册日期。

如何获得包含过去12个月每月注册数量计数的列表？像这样：

Month Count

1 1232

2 2222

3 122

4 4653

... ...

12 7654

我不习惯使用mysql，所以直到现在，我只知道如何计算去年的注册数量，而不是如何按最近12个月的数量进行分组。提前致谢！

更新

现在，我使用@fthiella解决方案来得到这个：

+------------------------------+-------------------------------+----------+

| Year(FROM_UNIXTIME(created)) | Month(FROM_UNIXTIME(created)) | Count(*) |

+------------------------------+-------------------------------+----------+

| 2012 | 4 | 9927 |

| 2012 | 5 | 5595 |

| 2012 | 6 | 4431 |

| 2012 | 7 | 3299 |

| 2012 | 8 | 429 |

| 2012 | 10 | 3698 |

| 2012 | 11 | 6208 |

| 2012 | 12 | 5142 |

| 2013 | 1 | 1196 |

| 2013 | 2 | 10 |

+------------------------------+-------------------------------+----------+

我如何强制查询给我计数为0的月份？

@fthiella解决方案(非常感谢！)：

SELECT y, m, Count(users.created)

FROM (

SELECT y, m

FROM

(SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,

(SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4

UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8

UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months) ym

LEFT JOIN users

ON ym.y = YEAR(FROM_UNIXTIME(users.created))

AND ym.m = MONTH(FROM_UNIXTIME(users.created))

WHERE

(y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))

OR

(yMONTH(CURDATE()))

GROUP BY y, m;

结果：

+------+----+----------------------+

| y | m | Count(users.created) |

+------+----+----------------------+

| 2012 | 5 | 5595 |

| 2012 | 6 | 4431 |

| 2012 | 7 | 3299 |

| 2012 | 8 | 429 |

| 2012 | 9 | 0 |

| 2012 | 10 | 3698 |

| 2012 | 11 | 6208 |

| 2012 | 12 | 5142 |

| 2013 | 1 | 1196 |

| 2013 | 2 | 10 |

| 2013 | 3 | 0 |

| 2013 | 4 | 0 |

+------+----+----------------------+