一、按照日期分组,返回连续的日期数据
前端会传统计类型type(1,2,3),比如近一周、近一月、近一年(12个月),我们先根据type生成日期集合,这将作为X轴:
List<String> dateList= selectDate(type,startDate,endDate);
private List<String> selectDate(String type,String startDate,String endDate){
if ("1".equals(type)){
//七天
return sevenDayList();
}else if("2".equals(type)){
//一个月
return thirtyDayList();
}else if("3".equals(type)){
//一年(12个月份)
return twelveMonthList();
}else if("4".equals(type)){
//指定时间段
return twoDatesList(startDate,endDate);
}else{
//默认七天
return sevenDayList();
}
}
//获取近七天的数据
public static List<String> sevenDayList(){
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
//获取一个星期前的时间
Calendar calendar = Calendar.getInstance();
calendar.setTime(new Date());
calendar.add(Calendar.DAY_OF_MONTH, -6);
Date startDate = calendar.getTime();
//获取当前时间时间
calendar.setTime(new Date());
Date endDate = calendar.getTime();
List<String> XDate = new ArrayList<String>();
boolean flag = true;
while (flag){
if(sdf1.format(startDate).equals(sdf1.format(endDate))){
XDate.add(sdf1.format(startDate));
flag = false;
}else{
XDate.add(sdf1.format(startDate));
Calendar cal = Calendar.getInstance();
cal.setTime(startDate);
cal.add(Calendar.DATE,1);
startDate =cal.getTime();
}
}
return XDate;
}
//近三十天
public static List<String> thirtyDayList(){
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
//获取一个月前的时间
Calendar calendar = Calendar.getInstance();
calendar.setTime(new Date());
calendar.add(Calendar.DAY_OF_MONTH, -29);
Date startDate = calendar.getTime();
//获取当前时间时间
calendar.setTime(new Date());
Date endDate = calendar.getTime();
List<String> XDate = new ArrayList<String>();
boolean flag = true;
while (flag){
if(sdf1.format(startDate).equals(sdf1.format(endDate))){
XDate.add(sdf1.format(startDate));
flag = false;
}else{
XDate.add(sdf1.format(startDate));
Calendar cal = Calendar.getInstance();
cal.setTime(startDate);
cal.add(Calendar.DATE,1);
startDate =cal.getTime();
}
}
return XDate;
}
/**
* 获取两个时间段内的日期
* @param sDate
* @param eDate
* @return
* @throws ParseException
*/
public static List<String> twoDatesList(String sDate,String eDate) {
try{
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
Date startDate = sdf1.parse(sDate);
Date endDate =sdf1.parse(eDate);
List<String> XDate = new ArrayList<String>();
boolean flag = true;
while (flag){
if(sdf1.format(startDate).equals(sdf1.format(endDate))){
XDate.add(sdf1.format(startDate));
flag = false;
}else{
XDate.add(sdf1.format(startDate));
Calendar cal = Calendar.getInstance();
cal.setTime(startDate);
cal.add(Calendar.DATE,1);
startDate =cal.getTime();
}
}
return XDate;
}catch (ParseException e){
}
return null;
}
二、中间service层和mapper层的参数传递就不在这里说了,直接将参数传入sql中:
<select id="getData" parameterType="com.xxx.dto.xxxDto" resultType="java.util.Map">
select
a.date,
ifnull(b.student, 0 ) as student,
ifnull(b.teacher, 0 ) as teacher
from
(
<foreach collection="xxxDto.dateList" index="index" item="item" open="(" separator="UNION ALL" close=")">
select #{item} date
</foreach>
) a
left join(
select
<choose>
<when test="type = 3">
DATE_FORMAT( r.CHECK_TIME, '%Y-%m' )
</when>
<when test="type != 3">
DATE_FORMAT( r.CHECK_TIME, '%Y-%m-%d' )
</when>
</choose>
date,sum( student),sum( teacher) low from record r
grouop by date
) b on a.date = b.date
order by date
</select>
sql中的foreach循环使用拼接的形式作为一个表 a,使用左连接的形式去匹配b表,目的就是统计在a表与b表在日期相同时的数据,如果b表没有a表对应的日期,那么对应的日期的数据量就是0,这里的 type !=3 代表的是近一周、近一月 type = 3 代表的是近一年
注意:可以是用java代码来处理逻辑,但是使用sql更加方便
二、不按照日期分组,比如按照类型、状态等字段分组
这个就比较简单了,因为不涉及到连续的日期,不需要上面那么处理日期这么麻烦,直接一个sql就可以搞定!
timeType (day、week、month、quarter、year)
SELECT
status,count(1)
FROM
t
WHERE
IS_DELETE = 0
<if test="startTime != null and startTime!=''">
AND DATE_FORMAT(yta.LAST_ALARM_TIME, '%Y-%m-%d %H:%i:%S') >= #{startTime}
</if>
<if test="endTime != null and endTime!=''">
AND DATE_FORMAT(LAST_ALARM_TIME, '%Y-%m-%d %H:%i:%S') <= #{endTime}
</if>
<if test="timeType != null and timeType != ''">
<choose>
<when test="timeType == 'day'">
AND date(yta.LAST_ALARM_TIME) = date(now())
</when>
<when test="timeType == 'week'">
AND LAST_ALARM_TIME >= DATE_SUB(now(), INTERVAL 1 WEEK)
</when>
<when test="timeType == 'month'">
AND LAST_ALARM_TIME >= DATE_SUB(now(), INTERVAL 1 MONTH)
</when>
<when test="timeType == 'quarter'">
AND LAST_ALARM_TIME >= DATE_SUB(now(), INTERVAL 1 QUARTER)
</when>
<when test="timeType == 'year'">
AND LAST_ALARM_TIME >= DATE_SUB(now(), INTERVAL 1 YEAR)
</when>
</choose>
</if>
grouop by status
如果status是数值1,2,3,想要显示文字类型
可以使用case when判断处理:
SELECT
CASE yta.STATUS
WHEN 0 THEN '未处置'
WHEN 1 THEN '处置中'
WHEN 2 THEN '已处置'
ELSE '未知' END AS name,
COUNT(1) as value FROM t
WHERE IS_DELETE = 0
或者判断字段是否为空:
SELECT
CASE WHEN NAME IS NOT NULL THEN NAME
ELSE ALARM_TYPE END AS name,
COUNT(1) as value FROM t