1、簡(jiǎn)單的，按月統(tǒng)計(jì)數(shù)量

SELECT CREATE_DATE, DATE_FORMAT(CREATE_DATE, '%Y-%m') AS month
    , COUNT(*) AS sum
FROM pt_user
GROUP BY month;

2、按月累加統(tǒng)計(jì)數(shù)據(jù)

SELECT a.month, SUM(b.total) AS total
FROM (
    SELECT DATE_FORMAT(CREATE_DATE, '%Y-%m') AS month, SUM(sum) AS total
    FROM (
        SELECT CREATE_DATE, DATE_FORMAT(CREATE_DATE, '%Y-%m') AS month
            , COUNT(*) AS sum
        FROM pt_user
        GROUP BY month
    ) ptuser
    GROUP BY month
) a
    JOIN (
        SELECT DATE_FORMAT(CREATE_DATE, '%Y-%m') AS month, SUM(sum) AS total
        FROM (
            SELECT CREATE_DATE, DATE_FORMAT(CREATE_DATE, '%Y-%m') AS month
                , COUNT(*) AS sum
            FROM pt_user
            GROUP BY month
        ) ptuser
        GROUP BY month
    ) b
    ON a.MONTH >= b.MONTH
GROUP BY a.MONTH
ORDER BY a.month;

3、多字段按月累加統(tǒng)計(jì)（eg：按租戶統(tǒng)計(jì)累加每月增長(zhǎng)數(shù)量）

SELECT a.ORGAN_ID, a.month, SUM(b.total) AS total
FROM (
    SELECT ORGAN_ID, DATE_FORMAT(CREATE_TIME, '%Y-%m') AS month
        , SUM(sum) AS total
    FROM (
        SELECT ORGAN_ID, CREATE_TIME, DATE_FORMAT(CREATE_TIME, '%Y-%m') AS month
            , COUNT(*) AS sum
        FROM pt_user
        GROUP BY month
    ) ptuser
    GROUP BY ORGAN_ID, month
) a
    JOIN (
        SELECT ORGAN_ID, DATE_FORMAT(CREATE_TIME, '%Y-%m') AS month
            , SUM(sum) AS total
        FROM (
            SELECT ORGAN_ID, CREATE_TIME, DATE_FORMAT(CREATE_TIME, '%Y-%m') AS month
                , COUNT(*) AS sum
            FROM pt_user
            GROUP BY month
        ) ptuser
        GROUP BY ORGAN_ID, month
    ) b
    ON a.ORGAN_ID = b.ORGAN_ID
        AND a.MONTH >= b.MONTH
GROUP BY a.MONTH
ORDER BY a.ORGAN_ID, a.month;

總結(jié)

以上是生活随笔為你收集整理的sql按月统计数量和按月累加统计数量的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。