mysql 日期范围查询

 时间段查询

select
                    ifnull(l.num,0) as value,
                    temp.dd as time
                    from (
                    select
                    ifnull(count(1),0) as num
                    ,date_format(bdr.create_time,'%Y%m%d') as time
                    from bil_location_record bdr
                    <where>
                        id IN ( SELECT max( id ) FROM bil_location_record GROUP BY waybill_no )
                        AND retrieval_status = '0'
                        <if test="sites != null and sites.size() > 0">
                            and bdr.storage_outlets_code in <foreach item="siteCode" collection="sites" open="(" separator="," close=")">#{siteCode}</foreach>
                        </if>
                    </where>
                    GROUP BY  date_format(bdr.create_time,'%Y%m%d')
                    ) l right join
                    (
                    select DATE_FORMAT(DATE_ADD( #{timeStart}, INTERVAL @i:=@i+1 DAY ),'%Y%m%d') as dd from (
                    select 1
                    <foreach item="index" collection="countArr">
                        union all select 1
                    </foreach>
                    ) as tmp,
                    (select @i:= -1) t
                    ) temp on temp.dd = l.time
                    ORDER BY temp.dd

 timeStart 是起始时间段，countArr 表示:起始日期到结束日期的时间段的天数是几天

本周查询

<!--             本周 星期-->
                    select
                    ifnull(l.num,0) as value,
                    CONCAT(temp.d) as time
                    from (
                    select
                    ifnull(count(1),0) as num
                    ,if(date_format(bdr.create_time,'%w')= 0,7,date_format(bdr.create_time,'%w')) as time
                    from bil_location_record bdr
                    <where>
                        id IN ( SELECT max( id ) FROM bil_location_record GROUP BY waybill_no )
                        AND retrieval_status = '0'
                        <if test="sites != null and sites.size() > 0">
                            and bdr.storage_outlets_code in <foreach item="siteCode" collection="sites" open="(" separator="," close=")">#{siteCode}</foreach>
                        </if>

                        and DATE_FORMAT(bdr.create_time,'%x%v')= DATE_FORMAT(now(),'%x%v')
                    </where>
                    GROUP BY  date_format(bdr.create_time,'%w')
                    ) l right join (select 1 as d UNION all select 2  as d UNION all select 3 as d  UNION all select 4 as d  UNION all select 5 as d  UNION all select 6  as d UNION all select 7 as d ) temp on l.time = temp.d
                    order by temp.d

 本月查询

<!--             本月 日-->
                    select
                    ifnull(l.num,0) as value,
                    CONCAT(temp.d)   as time
                    from (
                    select
                    ifnull(count(1),0) as num
                    ,date_format(bdr.create_time,'%e') as time
                    from bil_location_record bdr
                   <where>
                       id IN ( SELECT max( id ) FROM bil_location_record GROUP BY waybill_no )
                       AND retrieval_status = '0'
                       <if test="sites != null and sites.size() > 0">
                           and bdr.storage_outlets_code in <foreach item="siteCode" collection="sites" open="(" separator="," close=")">#{siteCode}</foreach>
                       </if>

                       and DATE_FORMAT(bdr.create_time,'%Y%m')= DATE_FORMAT(now(),'%Y%m')
                   </where>
                    GROUP BY  date_format(bdr.create_time,'%e')
                    ) l right join (select ls1.* from (select 1  as d UNION all select 2    UNION all select 3    UNION all select 4    UNION all select 5    UNION all select 6    UNION all select 7   UNION all select 8   UNION all select 9   UNION all select 10   UNION all select 11   UNION all select 12   UNION all select 13   UNION all select 14   UNION all select 15   UNION all select 16   UNION all select 17   UNION all select 18   UNION all select 19   UNION all select 20   UNION all select 21   UNION all select 22   UNION all select 23   UNION all select 24  UNION all select 25  UNION all select 26  UNION all select 27 UNION all select 28  UNION all select 29  UNION all select 30 UNION all select 31) ls1,(select DATE_FORMAT(LAST_DAY(now()),'%e') as lastDate) ls2 where ls1.d <![CDATA[<=]]> ls2.lastDate )
                    temp  on l.time = temp.d
                    order by temp.d

本年查询

 <!--             本年 月-->
                    select
                    ifnull(l.num,0) as value ,
                    CONCAT(temp.d) time
                    from (
                    select
                    ifnull(count(1),0) as num
                    ,date_format(bdr.create_time,'%c') as time
                    from bil_location_record bdr
                   <where>
                       id IN ( SELECT max( id ) FROM bil_location_record GROUP BY waybill_no )
                       AND retrieval_status = '0'
                       <if test="sites != null and sites.size() > 0">
                           and bdr.storage_outlets_code in <foreach item="siteCode" collection="sites" open="(" separator="," close=")">#{siteCode}</foreach>
                       </if>

                       and DATE_FORMAT(bdr.create_time,'%Y')= DATE_FORMAT(now(),'%Y')
                   </where>
                    GROUP BY  date_format(bdr.create_time,'%c')
                    ) l right join (select 1 as d UNION all select 2  UNION all select 3  UNION all select 4  UNION all select 5  UNION all select 6  UNION all select 7
                    union all select 8 union all select 9 union all select 10 union all select 11 union all select 12
                    ) temp on l.time = temp.d
                    order by temp.d