查询一段时间内的统计数据-统计没有数据的显示为0

MySQL查询连续一段时间内的统计数据，如果对应时间统计没有数据则显示为0，不能不显示对应的时间。
这里用java拼接SQL
传入两个日期时间startTime，endTime，对两个时间进行计算，得到日期的相差天数。

	public static int differentDaysByMillisecond(String date1,String date2){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date d1 = sdf.format(date1);
		Date d2 = sdf.format(date2);
		int days = (int) ((d2.getTime() - d1.getTime()) / (1000*3600*24))+1;
		return days;
	}

拼接间隔日期SQL

	StringBuffer param = new StringBuffer();
	for (int i = 0; i < day; i++) {
		if (i==0) {
			param.append("SELECT "+i);
		}else {
			param.append(" UNION SELECT "+i);
		}
	}
	StringBuffer sql = new StringBuffer();
	sql.append("SELECT xx.date, s.`count` from (SELECT DATE_FORMAT(DATE_SUB('2021-04-23', INTERVAL xc DAY), '%Y-%m-%d') as `date` FROM (SELECT @xi\\:=@xi + 1 as xc FROM ("+ param.toString() +") xcl, (SELECT @xi\\:= -1) xc0) xcxc) xx LEFT JOIN (SELECT COUNT(id) `count`,DATE_FORMAT(FROM_UNIXTIME(create_time), '%Y-%m-%d') `date` FROM student where 1=1 AND sex = '女') s ON xx.date = s.date where 1=1 AND xx.date BETWEEN '2021-04-10' AND '2021-04-23'");

最终拼接的执行SQL如下：

SELECT 
  xx.date,
  s.`count` 
FROM
  (SELECT 
    DATE_FORMAT(DATE_SUB('2021-04-23', INTERVAL xc DAY),'%Y-%m-%d') AS `date` 
  FROM
    (SELECT 
      @xi \ := @xi + 1 AS xc 
    FROM
      (SELECT 
        0 
      UNION
      SELECT 
        1 
      UNION
      SELECT 
        2 
      UNION
      SELECT 
        3 
      UNION
      SELECT 
        4 
      UNION
      SELECT 
        5 
      UNION
      SELECT 
        6) xcl,
      (SELECT 
        @xi \ := - 1) xc0) xcxc) xx 
  LEFT JOIN 
    (SELECT 
      COUNT(id) `count`,
      DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m-%d') `date` 
    FROM
      student 
    WHERE 1 = 1 
      AND sex = '女') s 
    ON xx.date = s.date 
WHERE 1 = 1 
  AND xx.date BETWEEN '2021-04-17' 
  AND '2021-04-23' 
  ORDER BY xx.date

最后查询数据如下：