根据创建日期获取每个月的运行总数

uujelgoq 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(418)

我想算出今年每个月的总收入。所以我的理想结果是：

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/#!20年9月23日

mysql sum cumulative-sum

来源：https://stackoverflow.com/questions/52610939/get-running-totals-for-each-month-based-on-created-at-date

1条答案

按热度按时间

goqiplq21#

试试这样的。

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

赞(0）回复(0）举报 2021-06-21

我来回答

根据创建日期获取每个月的运行总数

1条答案

相关问题

热门标签

最新问答