mysql - 如何计算每个月的记录数？

Question

我想计算一年中每个月的记录数。

select count(t2.user) as total, concat(YEAR(t2.docdate), '-', MONTH(t2.docdate)) as date

from tbl1 t1
LEFT JOIN tbl2 t2 ON(...)

WHERE

AND t2.docdate BETWEEN '2018-10-01' AND '2019-03-25'

GROUP BY MONTH(t2.docdate)

total docdate

但我的结果不正确。

18    2018-12-12
93    2018-12-12
51    2018-11-18
12    2018-11-07
13    2018-11-03

标签： mysql

Answer 1

您需要按您在 select 语句中包含的相同字段进行分组：

select count(t2.user) as total, concat(YEAR(t2.docdate), '-', MONTH(t2.docdate)) as date    
from tbl1 t1
LEFT JOIN tbl2 t2 ON(...)

WHERE

AND t2.docdate BETWEEN '2018-10-01' AND '2019-03-25'

GROUP BY concat(YEAR(t2.docdate), '-', MONTH(t2.docdate))