一、业务背景:
项目访问首页(统计页面),需统计近7周、近7月、近7年系统注册人数进行显示,要求显示对应的日期和记录数。
二、效果图如下:
三、sql语句展示如下(提示:以下以统计注册人数为例,展示近7周、近7月和近7年的sql语句):
A.统计近7周的注册人数sql语句:
<!--获取近七周的注册统计 -->
<select id="getSevenWeekRegister" resultType="java.util.Map">
SELECT '第一周' AS 'week', COUNT(*) AS COUNT FROM sys_user WHERE DATE_FORMAT(create_time,'%Y-%m-%d') BETWEEN (DATE_FORMAT((CURDATE() - INTERVAL 1 WEEK), '%Y-%m-%d')) AND (DATE_FORMAT((CURDATE()), '%Y-%m-%d'))
UNION SELECT '第二周' AS 'week', COUNT(*) AS COUNT FROM sys_user WHERE DATE_FORMAT(create_time,'%Y-%m-%d') BETWEEN (DATE_FORMAT((CURDATE() - INTERVAL 2 WEEK), '%Y-%m-%d')) AND (DATE_FORMAT((CURDATE() - INTERVAL 1 WEEK), '%Y-%m-%d'))
UNION SELECT '第三周' AS 'week', COUNT(*) AS COUNT FROM sys_user WHERE DATE_FORMAT(create_time,'%Y-%m-%d') BETWEEN (DATE_FORMAT((CURDATE() - INTERVAL 3 WEEK), '%Y-%m-%d')) AND (DATE_FORMAT((CURDATE() - INTERVAL 2 WEEK), '%Y-%m-%d'))
UNION SELECT '第四周' AS 'week', COUNT(*) AS COUNT FROM sys_user WHERE DATE_FORMAT(create_time,'%Y-%m-%d') BETWEEN (DATE_FORMAT((CURDATE() - INTERVAL 4 WEEK), '%Y-%m-%d')) AND (DATE_FORMAT((CURDATE() - INTERVAL 3 WEEK), '%Y-%m-%d'))
UNION SELECT '第五周' AS 'week', COUNT(*) AS COUNT FROM sys_user WHERE DATE_FORMAT(create_time,'%Y-%m-%d') BETWEEN (DATE_FORMAT((CURDATE() - INTERVAL 5 WEEK), '%Y-%m-%d')) AND (DATE_FORMAT((CURDATE() - INTERVAL 4 WEEK), '%Y-%m-%d'))
UNION SELECT '第六周' AS 'week', COUNT(*) AS COUNT FROM sys_user WHERE DATE_FORMAT(create_time,'%Y-%m-%d') BETWEEN (DATE_FORMAT((CURDATE() - INTERVAL 6 WEEK), '%Y-%m-%d')) AND (DATE_FORMAT((CURDATE() - INTERVAL 5 WEEK), '%Y-%m-%d'))
UNION SELECT '第七周' AS 'week', COUNT(*) AS COUNT FROM sys_user WHERE DATE_FORMAT(create_time,'%Y-%m-%d') BETWEEN (DATE_FORMAT((CURDATE() - INTERVAL 7 WEEK), '%Y-%m-%d')) AND (DATE_FORMAT((CURDATE() - INTERVAL 6 WEEK), '%Y-%m-%d'))
</select>
A.统计近7周的注册人数效果图:
B.统计近7月的注册人数sql语句:
<!--获取近七月的注册统计 -->
<select id="getSevenMonthRegister" resultType="java.util.Map">
SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `month`,COUNT(*) AS COUNT FROM sys_user WHERE DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 0 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM sys_user WHERE DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM sys_user WHERE DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM sys_user WHERE DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM sys_user WHERE DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM sys_user WHERE DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM sys_user WHERE DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m')
</select>
B.统计近7月的注册人数效果图:
C.统计近7年的注册人数sql语句:
<!--获取近七年的注册统计 -->
<select id="getSevenYearRegister" resultType="java.util.Map">
SELECT DATE_FORMAT(CURDATE(), '%Y') AS `year`,COUNT(*) AS COUNT FROM sys_user WHERE DATE_FORMAT(create_time,'%Y')=DATE_FORMAT((CURDATE() - INTERVAL 0 YEAR), '%Y')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 YEAR), '%Y') AS `year`,COUNT(*) FROM sys_user WHERE DATE_FORMAT(create_time,'%Y')=DATE_FORMAT((CURDATE() - INTERVAL 1 YEAR), '%Y')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 YEAR), '%Y') AS `year`,COUNT(*) FROM sys_user WHERE DATE_FORMAT(create_time,'%Y')=DATE_FORMAT((CURDATE() - INTERVAL 2 YEAR), '%Y')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 YEAR), '%Y') AS `year`,COUNT(*) FROM sys_user WHERE DATE_FORMAT(create_time,'%Y')=DATE_FORMAT((CURDATE() - INTERVAL 3 YEAR), '%Y')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 YEAR), '%Y') AS `year`,COUNT(*) FROM sys_user WHERE DATE_FORMAT(create_time,'%Y')=DATE_FORMAT((CURDATE() - INTERVAL 4 YEAR), '%Y')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 YEAR), '%Y') AS `year`,COUNT(*) FROM sys_user WHERE DATE_FORMAT(create_time,'%Y')=DATE_FORMAT((CURDATE() - INTERVAL 5 YEAR), '%Y')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 YEAR), '%Y') AS `year`,COUNT(*) FROM sys_user WHERE DATE_FORMAT(create_time,'%Y')=DATE_FORMAT((CURDATE() - INTERVAL 6 YEAR), '%Y')
</select>
C.统计近7年的注册人数效果图:
四:控制层和业务层相关代码(以下以7周为例):
A.Controller:
@ApiOperation("获取用户7周内的用户注册/学习/考试情况")
@PostMapping("/getSevenWeekUser")
public AjaxResult getSevenWeekUser(){
Map<String, Object> map = new HashedMap<String, Object>();
Long userId = SecurityUtils.getLoginUser().getUser().getUserId();
List<Map<String, Object>> resRegisterList = iAbGrzxWdxxjlService.getSevenWeekRegister();
List<Map<String, Object>> resStudyList = iAbGrzxWdxxjlService.getSevenWeekStudy();
List<Map<String, Object>> resTestList = iAbGrzxWdxxjlService.getSevenWeekTest();
List<String> stringList = new ArrayList<String>();
List<Long> rIntegers = new ArrayList<Long>();
List<Long> sIntegers = new ArrayList<Long>();
List<Long> tIntegers = new ArrayList<Long>();
for (Map<String, Object> mapR : resRegisterList) {
stringList.add(mapR.get("week").toString());
rIntegers.add((Long)mapR.get("COUNT"));
}
for (Map<String, Object> mapS : resStudyList) {
sIntegers.add((Long)mapS.get("COUNT"));
}
for (Map<String, Object> mapT : resTestList) {
tIntegers.add((Long)mapT.get("COUNT"));
}
map.put("xAxis", stringList);
map.put("resRegister", rIntegers);
map.put("resStudy", sIntegers);
map.put("resTest", tIntegers);
return AjaxResult.success(map);
}
B.Service:
//近7周:注册、学习、考试
List<Map<String, Object>> getSevenWeekRegister();
List<Map<String, Object>> getSevenWeekStudy();
List<Map<String, Object>> getSevenWeekTest();
五:小知识讲解
1.sql语句中使用的 UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
2.注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。