用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实现

    @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<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));
    }

测试结果：

[{"absenceCnt":0,"attnDate":"2022-09-03","companyId":1000,"expireCnt":1,"normalCnt":1},{"absenceCnt":1,"attnDate":"2022-09-02","companyId":1000,"expireCnt":0,"normalCnt":1}]

如果感觉对你有所帮助，烦请来个一键三连哦