获取MYSQL中本年度1月的记录

Question

如何在MySQL查询中获取本年度第一个月或第二个月的记录？

比如我想要1月份的数据，然后是2月份的数据：

select Sum(amount) as Amount from tbl_incomes where created_at = 'here the month January OR February of current year should be claused'

Answer 1

通过使用YEAR和MONTH，您可以使用以下内容

SELECT SUM(amount) AS Amount 
FROM tbl_incomes 
WHERE YEAR(created_at) = YEAR(NOW()) AND MONTH(created_at) IN (1, 2)

如果您希望将1月和2月的SUM放在单独的行中，您可以使用以下命令：

SELECT SUM(amount) AS Amount 
FROM tbl_incomes 
WHERE YEAR(created_at) = YEAR(NOW()) AND MONTH(created_at) IN (1, 2)
GROUP BY YEAR(created_at), MONTH(created_at)

Answer 2

您应该使用范围表达式。

SELECT sum(amount) amount
       FROM tbl_incomes
       WHERE created_at >= concat(year(now), '-01-01')
             AND created_at < concat(year(now), '-03-01');

这样，created_at上的索引就可以支持查询。