需要数据统计页面,肯定需要导出数据,于是,边学边写,完成了一段sql~
![9166166-14b91dae0506d09d.jpg](https://i-blog.csdnimg.cn/blog_migrate/15908e0b6c87c8c199e1fb3d11525621.webp?x-image-process=image/format,png)
统计报表 -- 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:线形图饼图绘制及时间等定制
![9166166-931fd471780b7e64.jpg](https://i-blog.csdnimg.cn/blog_migrate/8c72561e15bc8df2ec6d15d987b1204e.webp?x-image-process=image/format,png)
IT老五(it-lao5):关注公众号,一起源创,一起学习!