datetime - MySQL特定查询要求

Question

我在“sales”表中有日期时间字段名称“salestime”

我需要获取数据:

SalesMonthYear      Total
2010-11             10
2010-10             15
2010-09             21

或

Nov-2010            10
Oct-2010            15
Sep-2010            21

有人知道如何使用查询来实现这一目标吗？感谢您的帮助

Answer 1

您需要使用DATE_FORMAT和 GROUP BY 来实现你想要的。此查询将执行此操作:

SELECT DATE_FORMAT(salestime, '%Y-%m') as SalesMonthYear, count(*) as `Total` FROM `sales` GROUP BY SalesMonthYear ORDER BY `salestime`

对于第二个版本:

SELECT DATE_FORMAT(salestime, '%b-%Y') as SalesMonthYear, count(*) as `Total` FROM `sales` GROUP BY SalesMonthYear ORDER BY `salestime`