统计报表

admin管理员组

文章数量:1130349

统计报表

需要数据统计页面，肯定需要导出数据，于是，边学边写，完成了一段sql~

统计报表 -- sql统计语句

最早的版本是这样的：分三条sql查出三种不同的状态的记录数(总记录，未支付，已支付)

SELECT COUNT(*) AS RecordCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' 
SELECT COUNT(*) AS UnPayedCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' AND IsPayed = 0
SELECT COUNT(*) AS PayedCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' AND IsPayed = 1 

但是，还有一个需求，对记录数进行分日期进行统计，于是，需要改进，想到了用 GROUP BY 去实现; 但问题是CreateTime是DateTime, 接着百度之，找到了 CONVERT函数，于是变成了：

SELECT COUNT(*) AS RecordCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' GROUP BY CONVERT(CreateTime,DATE)
SELECT COUNT(*) AS UnPayedCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' AND IsPayed = 0 GROUP BY CONVERT(CreateTime,DATE)
SELECT COUNT(*) AS PayedCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' AND IsPayed = 1 GROUP BY CONVERT(CreateTime,DATE)

但是，为什么不能一条sql查出来呢？这样在便利性还是性能上应该都会有所提升,能否在COUNT带条件或者在SUM做判断？于是，最终的sql出现了:

SELECT CONVERT(CreateTime,DATE) AS RecordDate,COUNT(*) AS RecordCount, SUM(CASE WHEN IsPayed = 0 THEN 1 ELSE 0 END) AS UnPayedCount,SUM(CASE WHEN IsPayed = 0 THEN 1 ELSE 0 END) AS PayedCount,
FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00'
GROUP BY CONVERT(CreateTime,DATE)

原文：简书ThinkinLiu 博客: IT老五

相关报表图形绘制：统计报表 -- HignCharts：线形图饼图绘制及时间等定制

IT老五（it-lao5）：关注公众号，一起源创，一起学习！

统计报表

需要数据统计页面，肯定需要导出数据，于是，边学边写，完成了一段sql~

统计报表 -- sql统计语句

最早的版本是这样的：分三条sql查出三种不同的状态的记录数(总记录，未支付，已支付)

SELECT COUNT(*) AS RecordCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' 
SELECT COUNT(*) AS UnPayedCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' AND IsPayed = 0
SELECT COUNT(*) AS PayedCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' AND IsPayed = 1 

但是，还有一个需求，对记录数进行分日期进行统计，于是，需要改进，想到了用 GROUP BY 去实现; 但问题是CreateTime是DateTime, 接着百度之，找到了 CONVERT函数，于是变成了：

SELECT COUNT(*) AS RecordCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' GROUP BY CONVERT(CreateTime,DATE)
SELECT COUNT(*) AS UnPayedCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' AND IsPayed = 0 GROUP BY CONVERT(CreateTime,DATE)
SELECT COUNT(*) AS PayedCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' AND IsPayed = 1 GROUP BY CONVERT(CreateTime,DATE)

但是，为什么不能一条sql查出来呢？这样在便利性还是性能上应该都会有所提升,能否在COUNT带条件或者在SUM做判断？于是，最终的sql出现了:

SELECT CONVERT(CreateTime,DATE) AS RecordDate,COUNT(*) AS RecordCount, SUM(CASE WHEN IsPayed = 0 THEN 1 ELSE 0 END) AS UnPayedCount,SUM(CASE WHEN IsPayed = 0 THEN 1 ELSE 0 END) AS PayedCount,
FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00'
GROUP BY CONVERT(CreateTime,DATE)

原文：简书ThinkinLiu 博客: IT老五

相关报表图形绘制：统计报表 -- HignCharts：线形图饼图绘制及时间等定制

IT老五（it-lao5）：关注公众号，一起源创，一起学习！

本文标签： 统计报表