用Stream实现mysql的groupBy，sumCasewhen语法

　　由于最近项目上进行了大升级，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实现    @Test     public 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 list = new ArrayList();         list.add(userBO);         list.add(userBO1);         list.add(userBO2);         list.add(userBO3);          List 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 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));     }
　　测试结果：[{＂absenceCnt＂:0,＂attnDate＂:＂2022-09-03＂,＂companyId＂:1000,＂expireCnt＂:1,＂normalCnt＂:1},{＂absenceCnt＂:1,＂attnDate＂:＂2022-09-02＂,＂companyId＂:1000,＂expireCnt＂:0,＂normalCnt＂:1}]
　　如果感觉对你有所帮助，烦请来个一键三联哦