由于最近项目上进行了大升级,shardingsphere从3升级到了shardingsphere5.1.1,导致MyBatis中写的定制化的好多SQL语句报错:sytax error,很显然诸如case when这类的语法已经不支持了,没办法只能在内存中group by ,case when 后进行一个 sum 的操作。业务上就是统计状态字段出现的次数。
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
@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}]
如果感觉对你有所帮助,烦请来个一键三联哦