sql语句查询一天24小时每个小时数据，查询时间段内每天数据

统计条数

      String[] countArr = new String[23];
      //mapper层代码
      List<Map<String, Object>> selectData(@Param("countArr")String[] countArr);

查询当天24小时 sql 语句 

  SELECT
     count( u.id ) AS `value`,
     s.date AS text
     FROM
     (
     SELECT
     date_add( DATE_FORMAT( CURDATE( ), '%Y-%m-%d %H:%i:%s' ), INTERVAL @i := @i + 1 HOUR ) AS date
     FROM
     ( SELECT 1
        <foreach item = "index" collection = "countArr" >
        UNION ALL SELECT 1
        </foreach>
       ) AS tmp,
     ( SELECT @i := - 1 ) t
     ) s
     LEFT JOIN lsd_order u ON HOUR ( s.date ) = HOUR ( u.create_time )
     GROUP BY
     s.date

结果:

2.时间段内显示每一天

      int date = calcBetweenDate(startTime, endTime);
      String[] countArr = new String[date];

 calcBetweenDate 计算天数

public int calcBetweenDate(String start, String end) {
    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
    Date startDate = null;
    Date endDate = null;
    try {
      startDate = df.parse(start);
      endDate = df.parse(end);
    } catch (Exception e) {
      log.error("时间转换问题:"+e);
    }
    int count = (int) ((endDate.getTime() - startDate.getTime()) / (24 * 60 * 60 * 1000));
    return count;
  }

mapper层

  List<Map<String, Object>> selectData(@Param("startTime")String startTime,
                                          @Param("countArr")String[] countArr);

sql 语句

     SELECT
     count( u.id ) AS value,
     s.date AS text
     FROM
     (
     SELECT
     date_add(#{startTime}, INTERVAL @i := @i + 1 DAY)  AS date
     FROM
     ( SELECT 1
     <foreach item = "index" collection = "countArr" >
       UNION ALL SELECT 1
     </foreach>
     ) AS tmp,
     ( SELECT @i := - 1 ) t
     ) s
     LEFT JOIN lsd_order u ON s.date = date_format(u.create_time,'%Y-%m-%d')
     GROUP BY
     s.date