mysql之订单时间统计---按月---横向地区时间(每月的天数)统计,纵向时间(每月1号到最后那天)机柜订单统计

上一篇说了按照周进行统计,这篇说一下按照月统计,横向地区时间(每月的天数)统计,也就是说这个地区下的所有机柜在当前月产生了多少次订单,历史最大订单是按照当前年份计算(当前年下,某一个月的订单最大值获取出来),最小订单同理.

纵向时间(每月1号到最后那天)机柜订单统计,也就是说某台机柜从1号到当月最后那日的使用量统计,历史最大使用,也是按照年份进行为基准,统计今年所有1号的某台机器的最大值,一直到当月的最后那日.最小值同理.

1按月---横向地区时间(每月的天数)统计---当前月

<select id="monthRegionsByDeviceIdListNow" resultType="java.lang.String" parameterType="list">SELECTcount(1)FROM t_order  todWHEREDATE_FORMAT( tod.create_time, '%Y-%m' ) = DATE_FORMAT( CURDATE( ) , '%Y-%m' )AND  tod.USED_DEVICE_CODE IN<foreach collection="list" index="index" item="id" open="(" separator="," close=")">#{id}</foreach>
</select>

2按月---横向地区时间(每月的天数)统计---(本年历史最大值)

<select id="monthRegionsByDeviceIdListMax" resultType="java.lang.String" parameterType="list">SELECTcount( tod.create_time ) AS countFROM( SELECT  DATE_FORMAT( datelist, '%Y-%m' ) AS cad_month  FROM  calendarWHERE  DATE_FORMAT( datelist, '%Y-%m' ) &gt;=  #{beginTime}  AND DATE_FORMAT( datelist, '%Y-%m' )  &lt;= #{endTime}GROUP BY cad_month) AS cadLEFT JOIN( SELECT DATE_FORMAT( create_time, '%Y-%m' ) AS tod_month, create_time, USED_DEVICE_CODEFROM  t_orderWHERE  DATE_FORMAT( create_time, '%Y-%m' ) &gt;=  #{beginTime}  AND DATE_FORMAT( create_time, '%Y-%m' )  &lt;= #{endTime}   AND USED_DEVICE_CODE IN<foreach collection="list" index="index" item="id" open="(" separator="," close=")">#{id}</foreach>) AS todON cad.cad_month = tod.tod_monthGROUP BY cad.cad_monthORDER BY  count DESCLIMIT 1
</select>

注意:这里的开始时间和结束时间都是指本月1号到最后那日(也可以是从1号到当前日期),其它注意事项见前几篇

3按月---横向地区时间(每月的天数)统计---(本年历史最小值)

<select id="monthRegionsByDeviceIdListMin" resultType="java.lang.String" parameterType="list">SELECTcount( tod.create_time ) AS countFROM( SELECT  DATE_FORMAT( datelist, '%Y-%m' ) AS cad_month  FROM  calendarWHERE  DATE_FORMAT( datelist, '%Y-%m' ) &gt;=  #{beginTime}  AND DATE_FORMAT( datelist, '%Y-%m' )  &lt;= #{endTime}GROUP BY cad_month) AS cadLEFT JOIN( SELECT DATE_FORMAT( create_time, '%Y-%m' ) AS tod_month, create_time, USED_DEVICE_CODEFROM  t_orderWHERE  DATE_FORMAT( create_time, '%Y-%m' ) &gt;=  #{beginTime}  AND DATE_FORMAT( create_time, '%Y-%m' )  &lt;= #{endTime}   AND USED_DEVICE_CODE IN<foreach collection="list" index="index" item="id" open="(" separator="," close=")">#{id}</foreach>) AS todON cad.cad_month = tod.tod_monthGROUP BY cad.cad_monthORDER BY  count ASCLIMIT 1
</select>

第二部分:纵向时间(每月1号到最后那天)机柜订单统计

1纵向时间(每月1号到最后那天)机柜订单统计---(当前月)

<select id="monthTimeByDeviceIdListNow" resultType="java.util.HashMap" parameterType="list">SELECTDATE_FORMAT( cad.datelist, '%d' ) AS by_month_day,count(ot.create_time) AS countFROMcalendar AS cadLEFT JOIN (SELECT  DATE_FORMAT( tod.create_time, '%Y-%m-%d' ) AS tod_month,  tod.create_timeFROM  t_order todWHEREDATE_FORMAT( tod.create_time, '%Y-%m' ) = DATE_FORMAT( CURDATE( ), '%Y-%m' )AND tod.USED_DEVICE_CODE IN<foreach collection="list" index="index" item="id" open="(" separator="," close=")">#{id}</foreach>) AS ot ON cad.datelist = ot.tod_monthWHEREDATE_FORMAT( cad.datelist, '%Y-%m' ) = DATE_FORMAT( CURDATE( ), '%Y-%m' )GROUP BY cad.datelist
</select>

2纵向时间(每月1号到最后那天)机柜订单统计---(本年历史最大值)

<select id="monthTimeByDeviceIdListMax" resultType="java.util.HashMap" parameterType="list">SELECTDATE_FORMAT( t.datelist, '%d' ) AS by_month_day,MAX( count ) AS countFROM(SELECT  datelist, DATE_FORMAT( cad.datelist, '%Y-%d' ) AS by_month_day, count( create_time ) AS countFROM  calendar AS cadLEFT JOIN (SELECT  DATE_FORMAT( create_time, '%Y-%m-%d' ) AS tod_month,  create_timeFROM  t_order todWHERE   DATE_FORMAT( create_time, '%Y-%m-%d' )  &gt;=  #{beginTime}AND DATE_FORMAT( create_time, '%Y-%m' ) &lt;= #{endTime}AND tod.USED_DEVICE_CODE IN<foreach collection="list" index="index" item="id" open="(" separator="," close=")">#{id}</foreach>) AS od ON od.tod_month = cad.datelistWHERE   DATE_FORMAT( cad.datelist, '%Y-%m' )  &gt;=  #{beginTime}AND DATE_FORMAT( cad.datelist, '%Y-%m' ) &lt;= #{endTime}GROUP BY  datelist) AS tGROUP BYt.by_month_day
</select>

3纵向时间(每月1号到最后那天)机柜订单统计---(本年历史最小值)

<!-- 时间为本年第一天到本月  -->
<select id="monthTimeByDeviceIdListMin" resultType="java.util.HashMap" parameterType="list">SELECTDATE_FORMAT( t.datelist, '%d' ) AS by_month_day,MIN( count ) AS countFROM(SELECT  datelist, DATE_FORMAT( cad.datelist, '%Y-%d' ) AS by_month_day, count( create_time ) AS countFROM  calendar AS cadLEFT JOIN (SELECT  DATE_FORMAT( create_time, '%Y-%m-%d' ) AS tod_month,  create_timeFROM  t_order todWHERE   DATE_FORMAT( create_time, '%Y-%m-%d' )  &gt;=  #{beginTime}AND DATE_FORMAT( create_time, '%Y-%m' ) &lt;= #{endTime}AND tod.USED_DEVICE_CODE IN<foreach collection="list" index="index" item="id" open="(" separator="," close=")">#{id}</foreach>) AS od ON od.tod_month = cad.datelistWHERE   DATE_FORMAT( cad.datelist, '%Y-%m' )  &gt;=  #{beginTime}AND DATE_FORMAT( cad.datelist, '%Y-%m' ) &lt;= #{endTime}GROUP BY  datelist) AS tGROUP BYt.by_month_day
</select>