SQL查询数据,要求日期不间断，没有数据的日期显示0

 <select id="getTaskPublish" resultType="java.util.Map">
        SELECT
        count( u.id ) AS count,
        s.date
        FROM
        (
        SELECT
        date_add( #{date}, 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 task_supervise u
        ON s.date = date_format( u.create_time, '%Y-%m-%d' )
        AND u.state != '1'
        GROUP BY
        s.date
    </select>

　　

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

　　date[0]为开始时间，date[1]为结束时间

 @Override
    public List<Map<String, Object>> getTaskPublish(String[] date) {
        int num = calcBetweenDate(date[0], date[1]);
        String[] countArr = new String[num];
        return homePageMapper.getTaskPublish(date[0], countArr);
    }

　　

    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) {
            System.out.println("日期转换出错");
        }
        assert endDate != null;
        return (int) ((endDate.getTime() - startDate.getTime()) / (24 * 60 * 60 * 1000));
    }