• sql按月统计数量和按月累加统计数量


    1、简单的,按月统计数量

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

    2、按月累加统计数据

    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、多字段按月累加统计(eg:按租户统计累加每月增长数量)

    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;
  • 相关阅读:
    Windows下好用的git客户端--GitExtentions
    高分辨率下放大netbeans中的小图标
    小书匠使用手册
    win8 telnet VirtualBox中的redhat9
    win8安装新字体
    netbeans设置字体
    win7下Chrome有两个图标的解决方法
    【转】HDU-6035-Colorful Tree
    POJ1703--Find them, Catch them(种类并查集)
    KMP的妙用(利用next数组寻找字符串的循环节)
  • 原文地址:https://www.cnblogs.com/wanglu/p/8954409.html
Copyright © 2020-2023  润新知