江苏龙网

  1. 主页 > 生活百科 > >

用Stream实现mysql的groupBy, sum Case when 语法

小知识

由于最近项目上进行了大升级，shardingsphere从3升级到了shardingsphere5.1.1,导致MyBatis中写的定制化的好多SQL语句报错：sytax error，很显然诸如case when这类的语法已经不支持了，没办法只能在内存中group by ,case when 后进行一个 sum 的操作 。业务上就是统计状态字段出现的次数 。
SQL语句的原型：select company_id,business_date,SUM(CASE status WHEN 0 THEN 1 else 0 end) zeroCnt,SUM(CASE status WHEN 1 THEN 1 else 0 end) twoCnt,SUM(CASE status WHEN 2 THEN 1 else 0 end) thereCnt from table group by company_id, business_date通过Stream实现@Testpublic void testStreamGroupBySum(){UserBO userBO = new UserBO(1000, "2022-09-02", 0);UserBO userBO1 = new UserBO(1000, "2022-09-02", 1);UserBO userBO2 = new UserBO(1000, "2022-09-03", 2);UserBO userBO3 = new UserBO(1000, "2022-09-03", 0);ArrayList<UserBO> list = new ArrayList<UserBO>();list.add(userBO);list.add(userBO1);list.add(userBO2);list.add(userBO3);List<UserStatistic> userStatistics = list.stream().collect(Collectors.groupingBy(UserBO::getAttnDate)).entrySet().stream().map(entry -> {UserStatistic userStatistic = new UserStatistic();if (!CollectionUtils.isEmpty(entry.getValue())) {userStatistic.setCompanyId(entry.getValue().get(0).getCompanyId());}userStatistic.setAttnDate(entry.getKey());Map<Integer, Long> map = entry.getValue().stream().collect(Collectors.groupingBy(UserBO::getStatus, Collectors.counting()));userStatistic.setNormalCnt(map.getOrDefault(UserStatusEnum.NORMAL.getKey(), 0L));userStatistic.setAbsenceCnt(map.getOrDefault(UserStatusEnum.ABSENCE.getKey(), 0L));userStatistic.setExpireCnt(map.getOrDefault(UserStatusEnum.EXPIRE.getKey(), 0L));return userStatistic;}).sorted().collect(Collectors.toList());System.out.println(JSON.toJSONString(userStatistics));}【用Stream实现mysql的groupBy, sum Case when 语法】测试结果：
[{"absenceCnt":0,"attnDate":"2022-09-03","companyId":1000,"expireCnt":1,"normalCnt":1},{"absenceCnt":1,"attnDate":"2022-09-02","companyId":1000,"expireCnt":0,"normalCnt":1}]如果感觉对你有所帮助，烦请来个一键三联哦




    推荐阅读


    上一篇：动手分析SQL Server中的事务中使用的锁

    下一篇：游戏沉迷怎么办 沉迷游戏无法自拔了,怎么办?