mysql统计近7天，近30天数据，echarts展示

最新推荐文章于 2023-06-09 03:42:00 发布

其实啥也不是

最新推荐文章于 2023-06-09 03:42:00 发布

阅读量1.8k

点赞数 1

分类专栏：数据库 springboot 文章标签： mysql spring

本文链接：https://blog.csdn.net/qq_38410147/article/details/117475358

版权

数据库同时被 2 个专栏收录

6 篇文章

订阅专栏

springboot

2 篇文章

订阅专栏

<select id="selectStatsByMap" resultType="map" parameterType="map">
    select DATE_FORMAT(mycreatedate,'%m-%d') date0,ifnull(tt.count0, 0) count0
from (select curdate() mycreatedate union
select DATE_SUB(curdate(),INTERVAL 1 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 2 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 3 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 4 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 5 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 6 DAY) mycreatedate 
<if test="dateType == '2'.toString()">
union
select DATE_SUB(curdate(),INTERVAL 7 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 8 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 9 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 10 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 11 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 12 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 13 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 14 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 15 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 16 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 17 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 18 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 19 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 20 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 21 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 22 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 23 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 24 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 25 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 26 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 27 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 28 DAY) mycreatedate union
select DATE_SUB(curdate(),INTERVAL 29 DAY) mycreatedate
</if>
) tt0
LEFT JOIN (SELECT DATE_FORMAT(t.create_time,'%m-%d') date0,count(t.mec_id) count0 FROM exec_record t where 1=1
<if test="dateType == '1'.toString()">
and DATE_FORMAT(t.create_time,'%Y-%m-%d')>DATE_SUB(CURDATE(), INTERVAL 7 DAY)
</if>
<if test="dateType == '2'.toString()">
and DATE_FORMAT(t.create_time,'%Y-%m-%d')>DATE_SUB(CURDATE(), INTERVAL 30 DAY)
</if>
 GROUP BY date0) tt
on tt.date0 = DATE_FORMAT(tt0.mycreatedate,'%m-%d')
ORDER BY date0
</select>

dateType=1为7天内的统计数据

dateType=2为30天内的统计数据

mapper层

List<Map<String, Object>> selectStatsByMap(Map<String, Object> map);

service层，处理数据遍历,

List<Map<String, Object>>  maps = visitExecCustomerMapper.selectStatsByMap(map);
List<String> days = new ArrayList<>();
List<String> counts0 = new ArrayList<>();
for (Map m:maps){
    String date0 = MapUtils.getString(m,"date0");
    String count0 = MapUtils.getString(m,"count0");
    days.add(date0);
    counts0.add(count0);
 }
map.put("days",days);
map.put("counts0",counts0);

echars柱状图

days对应x轴

counts对应y轴