sql按月统计数量和按月累加统计数量
2024-09-01 17:17:43
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;
最新文章
- 安装yum
- HTML5系列：HTML5结构
- C#中一种可调用的异常处理方法
- VB6 GDI+ 入门教程[7] Graphics 其他内容
- Excel取消保护密码
- Android Spinner(级联 天气预报）
- Oracle数据库游标的类型
- delphi中设置系统时间方法
- mysql触发器的使用
- Centos6.4三种更改hostname的方法之间的对比
- 关于用wubi安装Ubuntu，总是提示“没有定义根文件系统”的问题
- 会声会影小成果分享（那段青春岁月）——校学习部宣传视频制作&生日祝福
- pip 安装 和 卸载 django
- oracle io 等待图解
- AT2567 RGB Sequence dp
- sublime text3 快捷键和好用的插件
- 2018年3月最新的Ubuntu 16.04.4漏洞提权代码
- 【译】SQLskills SQL101：Trace Flags、ERRORLOG、Update Statistics
- Android Material Design控件学习（二）——NavigationView的学习和使用
- 源码分析三(Vector与ArrayList的区别)
热门文章
- vue 学前班003（生命周期）
- R软件常用命令
- centos7.2 get pid by process name with python3.6
- RxJava/RxAndroid 使用实例实践
- android学习-LocationManager（一）-定位方式原理解析
- 全局描述符表（GDT）——《x86汇编语言：从实模式到保护模式》读书笔记09
- 解决MySQL联表时出现字符集不一样
- [LeetCode]LinkedList Cycle
- Installing haproxy load balancing for http and https--转载
- Ripple(瑞波币)validator-keys-tool 配置验证器