mysql查询7天前数据,补0

统计如下这张表7天前数据

结果

Controller层

@GetMapping("/getChartData")publicList<DailyUserStaticChart>getChartData(){//获取7天前数据List<DailyUserStaticChart>dailyUserStaticCharts=userDailyStaticService.getChartData();returndailyUserStaticCharts;}

entity

@DatapublicclassDailyUserStaticChart{privateLocalDatedayTime;privateIntegernewUserCount;privateIntegeractiveUserCount;}

sql

SELECT day_list.day_time,IFNULL(user_data.new_user_count,0)new_user_count,IFNULL(user_data.active_user_count,0)active_user_count FROM(SELECT@DATE:=DATE_SUB(@DATE,INTERVAL1DAY)day_time FROM(SELECT@DATE:=DATE_ADD(CURDATE(),INTERVAL1DAY)FROM studentLIMIT7)days)day_listLEFTJOIN(SELECTstat_date,new_user_count,active_user_countFROMuser_daily_static)user_data ON day_list.day_time=user_data.stat_date