工作知识总结

遇到的难题

1.5月26号难题

1.描述

1.1 功能图片描述
1.2 遇到的问题 如何通过mysql 语句一次性将返回条件的数据查出’

1.3 思路:首先想到的是如何根据前端给的时间点(开始和结束时间)找到多少天

用到了 Calendar类

参考文献 https://blog.csdn.net/yx0628/article/details/79317440

工具类代码

 /**
 * 获取某个时间段的每一天
 */
public static List<String> findDaysStr(String begintTime, String endTime) {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    Date dBegin = null;
    Date dEnd = null;
    try {
        dBegin = sdf.parse(begintTime);
        dEnd = sdf.parse(endTime);
    } catch (ParseException e) {
        e.printStackTrace();
    }
    List<String> daysStrList = new ArrayList<String>();
    daysStrList.add(sdf.format(dBegin));
    Calendar calBegin = Calendar.getInstance();
    calBegin.setTime(dBegin);
    Calendar calEnd = Calendar.getInstance();
    calEnd.setTime(dEnd);
    while (dEnd.after(calBegin.getTime())) {
        calBegin.add(Calendar.DAY_OF_MONTH, 1);
        String dayStr = sdf.format(calBegin.getTime());
        daysStrList.add(dayStr);
    }
    return daysStrList;
}

现在获取到有多少天了 然后怎么获取每天的数据呢

参考文献 mysql获取近7天每天数据，无数据补0

仿照写了 如下sql

     SELECT IFNULL(b.count,0) AS COUNT ,a.clickDate
     FROM (
     SELECT '2021-05-24' AS clickDate 
    UNION ALL
    SELECT DATE_SUB('2021-05-24', INTERVAL 1 WEEK) AS clickDate
    UNION ALL
    SELECT DATE_SUB('2021-05-24', INTERVAL 2 WEEK) AS clickDate
    UNION ALL
    SELECT DATE_SUB('2021-05-24', INTERVAL 3 WEEK) AS clickDate
    UNION ALL
    SELECT DATE_SUB('2021-05-24', INTERVAL 4 WEEK) AS clickDate
    UNION ALL
    SELECT DATE_SUB('2021-05-24', INTERVAL 5 WEEK) AS clickDate
    UNION ALL
    SELECT DATE_SUB('2021-05-24', INTERVAL 6 WEEK) AS clickDate
   ) AS a
   LEFT JOIN (
   SELECT DATE(user_order.order_create_time(#注释:字段名(当前时间))) AS orderCreateTime, COUNT(*) AS COUNT
  FROM user_order(表名)
  GROUP BY DATE(user_order.order_create_time)
  ) b
  ON a.clickDate = b.orderCreateTime

了解了union all 的用法 参考文献 : sql 里union all 与 union 的区别

然后发现:

这个地方需求的是不定时间原来的固定时间点sql不行

去了解了 mybits结合sql 批量查询 参考文献 mybits结合sql 批量查询

最后 mybits 代码如图

<select id="selectDayCountByPrdId" resultType="com.paperpass.backstage.util.PageData">
  SELECT IFNULL(b.count,0) AS COUNT ,a.clickDate
  FROM (
      SELECT #{time} AS clickDate
    <foreach collection="count" index="index" item="item" open="" close="" separator="">
      UNION ALL
      SELECT DATE_SUB(#{time}, INTERVAL #{item} DAY) AS clickDate
    </foreach>
 ) AS a
 LEFT JOIN (
  SELECT DATE(user_order.order_create_time) AS orderCreateTime, COUNT(*) AS COUNT
  FROM user_order
  GROUP BY DATE(user_order.order_create_time)
 ) b
 ON a.clickDate = b.orderCreateTime
 </select>

<foreach collection="count" index="index" item="item" open="" close="" separator="">
  UNION ALL
  SELECT DATE_SUB(#{time}, INTERVAL #{item} DAY) AS clickDate
</foreach>

 方法介绍  collection  指代 传入集合  map list 名字  index 索引  item 循环取出来的数  open 当前语句 左符号
 close 当前语句  右符号  separator   是否需要 ,

代码

    PageData pageData = getPageData();
    //获取前端开始时间
    String startTime = pageData.getString("startTime");
    //获取前端结束时间
    String endTime = pageData.getString("endTime");
    //获取每天时间
    List<String> daysStr = Tools.findDaysStr(startTime, endTime);
    List<Integer> count=new ArrayList<>();
    for (int i = 1; i < daysStr.size(); i++) {
        count.add(i);
    }
    List<PageData> pageDataList = userOrderMapper.selectDayCountByPrdId(endTime, count);