如何使用MySQL查询仅在本月使用源ID汇总

Question

我在mysql

中有此表

source amount for_date
1        100   2019-01-15
1        200   2019-01-16
1        200   2019-02-16
2        350   2019-02-14
SELECT SUM(amount) amountSum FROM Flow WHERE source='1' GROUP BY month(for_date)

此MySQL查询工作，但我只想获得当前月，而不是前一个月。

Answer 1

用 where条件过滤

一个月

SELECT SUM(amount) amountSum FROM Flow WHERE source='1' AND month(for_date)=month(now()) GROUP BY month(for_date)

with Year

 SELECT SUM(amount) amountSum FROM Flow WHERE source='1' AND month(for_date)=month(now()) AND year(for_date)=year(now()) GROUP BY month(for_date)

Answer 2

如果将本月定义为今天日期的月份，那么您甚至不需要在此处汇总：

SELECT SUM(amount) amountSum
FROM Flow
WHERE
    source = '1' AND
    DATE_FORMAT(for_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m');

如果您将当前月定义为桌上的最新月份，那么我们可以在您当前查询中使用LIMIT：

SELECT SUM(amount) amountSum
FROM Flow
WHERE source = '1'
GROUP BY DATE_FORMAT(for_date , '%Y-%m')
ORDER BY DATE_FORMAT(for_date , '%Y-%m') DESC
LIMIT 1;

Answer 3

我会在查询中添加两个日期参数：

SELECT amount FROM table WHERE for_date>='2019-01-01' AND for_date<='2019-01-31'

Answer 4

尝试此查询

SELECT SUM(amount) amountSum FROM Flow WHERE source='1' GROUP BY DATE_FORMAT(for_date, "%m-%y")

，如果您的数据库生长并且有多年的时间，则将按月/年进行组合。

Answer 5

SELECT SUM(amount) amountSum FROM Flow WHERE source='1' and DATE_FORMAT(for_date,'%Y-%m') = DATE_FORMAT(CURRENT_DATE(),'%Y-%m')

组的用途是什么？如果您只想要当月？你为什么要使用组？运行上述查询，没有...

Answer 6

尝试此

     SELECT SUM(amount) FROM Flow WHERE source='1' AND MONTH(for_date) = MONTH(CURRENT_DATE()) AND YEAR(for_date)=YEAR(CURRENT_DATE());