Java和MySQL統計本周本月本季和年 · Java學習筆記

## 表信息 login_location 登錄地區北京, 上海 loginTime 時間 datetime 2020-01-01 返回結果會補日期, 比如本周, 只有周一的數據, 則也會返回周二到周日的數據, 數量統計為0, 其他一樣會補日期 ## 本周純SQL ``` SELECT a.DAYS as loginTime, count(login_location) num FROM ( SELECT DATE_FORMAT(date, '%Y-%m-%d') AS DAYS FROM ( SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE()) ) DAY AS date UNION ALL SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE()) - 1) DAY AS date UNION ALL SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE()) - 2) DAY AS date UNION ALL SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE()) - 3) DAY AS date UNION ALL SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE()) - 4) DAY AS date UNION ALL SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE()) - 5) DAY AS date UNION ALL SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE()) - 6) DAY AS date ) AS week_dates ) a LEFT JOIN sys_logininfor ON LEFT ( login_time, 10 ) = a.DAYS GROUP BY a.DAYS ORDER BY loginTime; ``` ## 本月純SQL ``` SELECT a.DAYS as loginTime, count(login_location) num FROM ( SELECT date_add( DATE_ADD(CURDATE(), INTERVAL - DAY ( CURDATE() ) DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY ) as DAYS FROM mysql.help_topic WHERE help_topic_id < DAY ( last_day( CURDATE() )) ORDER BY help_topic_id ) a LEFT JOIN sys_logininfor ON LEFT ( login_time, 10 ) = a.DAYS GROUP BY a.DAYS ORDER BY loginTime ``` ## 本季純SQL ``` SELECT a.DAYS as loginTime, count(login_location) num FROM ( SELECT date_add( DATE_ADD(CURDATE(), INTERVAL - DAYOFYEAR ( CURDATE() ) DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) ) DAY ) as DAYS FROM mysql.help_topic WHERE help_topic_id <= DAYOFYEAR( LAST_DAY(concat(year(CURDATE()), '-', lpad(QUARTER(CURDATE()) *3,2,0), '-01'))) and help_topic_id >= DAYOFYEAR( concat(year(CURDATE()), '-', lpad((QUARTER(CURDATE()) - 1) *3+1,2,'0'), '-01')) ORDER BY help_topic_id ) a LEFT JOIN sys_logininfor ON LEFT ( login_time, 10 ) = a.DAYS GROUP BY a.DAYS ORDER BY loginTime ``` ## 查詢指定年份, 按月返回 MySQL查詢出有數據的日期, Java補充缺失的日期, 工具類是之前項目里的 ``` SELECT date_format( login_time, '%Y-%m' ) AS loginTime, count( login_location ) AS num FROM sys_logininfor WHERE date_format( login_time, '%Y' ) = date_format( now(), '%Y' ) GROUP BY loginTime MonthUtil util = new MonthUtil(); List<String> monthArr = util.monthArryear(LocalDate.now().getYear() + ""); List<LoginTimeVo> loginYears = baseMapper.getLoginYears(year); monthArr.forEach(month -> { // 沒有這個日期 boolean b = loginYears.stream().noneMatch(s -> Objects.equals(month, s.getLoginTime())); if (b) { LoginTimeVo vo = new LoginTimeVo(); vo.setLoginTime(month); vo.setNum(0); loginYears.add(vo); } }); loginYears.sort(Comparator.comparing(LoginTimeVo::getLoginTime)); return loginYears; ``` 工具類 MonthUtil ``` import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.List; public class MonthUtil { public List<String> monthArr() { SimpleDateFormat format = new SimpleDateFormat("yyyy-MM"); Calendar c; List<String> monthArr = new ArrayList<>(); for (int i = 11; i >= 0; i--) { c = Calendar.getInstance(); c.add(Calendar.MONTH, -i); Date start = c.getTime(); monthArr.add(format.format(start)); } return monthArr; } public List<String> monthArryear(String year) { List<String> monthArr = new ArrayList<>(); String month = ""; for (int i = 1; i <= 12; i++) { month = i + ""; if (month.length() == 1) { month = '0' + month + ""; } monthArr.add(year + "-" + month); } return monthArr; } } ```