MySql教程

MySQL根据日期进行查询数据,没有数据也要显示空

本文主要是介绍MySQL根据日期进行查询数据,没有数据也要显示空,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

控制层: 

	/**
	 * 首页-用户数量统计
	 */
	@GetMapping("/countNum")
	@ApiOperationSupport(order = 2)
	@ApiOperation(value = "用户数量统计", notes = "传入doctorUserVO")
	public R countNum(DoctorUserVO doctorUserVO) {
		doctorUserVO.setTenantId(CommonUtils.getTenantId(request));
		Map<String, Object> countOneDayNum = userService.countOneDayNum(doctorUserVO);
		int countAllDayNum = userService.count(new QueryWrapper<DoctorUser>().eq("tenant_id",doctorUserVO.getTenantId()));
		Map<String, Object> map = new HashMap<>();
		map.put("count", countOneDayNum.get("count"));
		map.put("day", countOneDayNum.get("day"));
		map.put("total", countAllDayNum);
		return R.data(map);

	}

服务层接口

Map<String, Object> countOneDayNum(DoctorUserVO doctorUserVO);

服务层实现

	@Override
	public Map<String, Object> countOneDayNum(DoctorUserVO doctorUserVO) {
		List<DoctorUserVO> countOneDay = baseMapper.countOneDayNum(doctorUserVO);
		Map<String, Integer> map = new HashMap<>();
		Map<String, Integer> mapMonth = new LinkedHashMap<>();
		Map<String, Object> res = new HashMap<>();


		Calendar calendar = Calendar.getInstance();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

		for (DoctorUserVO doctorUser : countOneDay) {
			map.put(sdf.format(doctorUser.getCreateTime()), doctorUser.getCount());

		}

		for (int i = 0; i < 30; i++) {

			if (String.valueOf(map.keySet()).indexOf(sdf.format(calendar.getTime())) != -1) {
				mapMonth.put(sdf.format(calendar.getTime()), map.get(sdf.format(calendar.getTime())));
			} else {
				mapMonth.put(sdf.format(calendar.getTime()), 0);
			}
			calendar.add(Calendar.DAY_OF_MONTH, -1);
		}

		res.put("day", mapMonth.keySet());
		res.put("count", mapMonth.values());

		return res;

	}

Mapper接口

	List<DoctorUserVO> countOneDayNum(DoctorUserVO doctorUserVO);

Mapper.xml

    <select id="countOneDayNum" resultMap="userResultMap">
        SELECT
            DATE_FORMAT( du.create_time, '%Y-%m-%d' ) AS create_time,
            COUNT(*) AS count
        FROM
            doctor_user du
        WHERE
                du.tenant_id = #{tenantId}
            AND DATE_FORMAT(du.create_time, '%Y%m') <![CDATA[ = ]]> DATE_FORMAT( CURDATE( ) , '%Y%m' )
        GROUP BY
            create_time
    </select>

返回数据结构: 

 

这篇关于MySQL根据日期进行查询数据,没有数据也要显示空的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!