由于最近项目上进行了大升级,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}]
如果感觉对你有所帮助,烦请来个一键三联哦
免责声明:本站所有文章内容,图片,视频等均是来源于用户投稿和互联网及文摘转载整编而成,不代表本站观点,不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益,请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://yundeesoft.com/5965.html