我试图获得今年每个月的运行总数。所以我的理想结果是这样的:
January | 4
February | 5
March | 8
April | 10
May | 12
June | 14
July | 16
August | 17
September | 18
October | 21
November | 22
December | 22
基本计数只会检查一个月的第一天,例如:
January 1 (sum where created_at < January 1, 2018)
February 1 (sum where created_at < February 1, 2018)
March 1 (sum where created_at < March 1, 2018)
April 1 (sum where created_at < April 1, 2018)
...
一次做一个月很容易,因为我可以这样做:
SELECT *
FROM businesses
WHERE created_at < CONCAT(YEAR(CURDATE()), "-12-01 00:00:01")
我尝试使用另一个 Stackoverflow 答案中的一个示例,但它并没有像预期的那样工作,因为它看起来像是排序或某些东西搞砸了,所以计数没有正确排列。
您可以在此处查看架构构建和当前 SQL:http : //sqlfiddle.com/#!9/0c23cc/20
尝试这样的事情。
SELECT
MONTHNAME( created_at ) AS theMonth,
(
SELECT
count( * )
FROM
businesses AS u1
WHERE
YEAR ( u1.created_at ) = YEAR ( businesses.created_at )
AND u1.created_at <= date_ADD( last_day( businesses.created_at ), INTERVAL 1 DAY )
) AS totals
FROM
businesses
WHERE
YEAR ( businesses.created_at ) = YEAR ( CURDATE( ) )
GROUP BY
MONTHNAME( businesses.created_at )
ORDER BY
MONTH ( businesses.created_at ) ASC
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句