首先看一段 sql:按省份分组,把邮件号用逗号拼接,最后按省份名的首字母排序
select t.PROVINCE_NAME provinceName,
WM_CONCAT(t.mail_no) otherMailNoListStr
from INVOICE t
where t.INVOICE_TYPE != 2
and t.PAYMENT_MODE != 2
and t.PROVINCE_NAME is not null
${ew.sqlSegment}
group by t.PROVINCE_NAME
order by t.PROVINCE_NAME
这段 sql 在数据量为百万级别的情况下,查询时间贼慢。。。也导致一上线项目直接加载超时,没办法只能把具体功能用代码去实现了,好吧~
show you code
sql:
select t.mail_no mailNo, t.PROVINCE_NAME provinceName from INVOICE t
Java:
List<InvoiceForProStatistics> selectInvoiceForProStatisticsToMailList = userInvoiceMapper.selectInvoiceForProStatisticsToMailList(ew);
List<InvoiceForProStatistics> arrayList = new ArrayList<>();
Map<String, String> collect = selectInvoiceForProStatisticsToMailList.stream().collect(
Collectors.groupingBy(InvoiceForProStatistics::getProvinceName,
Collectors.mapping(InvoiceForProStatistics::getMailNo,
Collectors.joining(","))));
collect.forEach((k, v) -> {
InvoiceForProStatistics a = new InvoiceForProStatistics();
a.setProvinceName(k);
a.setMailNo(v);
arrayList.add(a);
});
Collections.sort(arrayList,(InvoiceForProStatistics o1, InvoiceForProStatistics o2)-> Collator.getInstance(Locale.CHINESE).compare(o1.getProvinceName(),o2.getProvinceName()));
最后的 arrayList 和开头sql 执行出来的结果集是一样的,执行速度也是杠杠的,同事也对 Java1.8 的 stream 有了更深的了解。