关于 oracle的WM_CONCAT函数用代码实现

首先看一段 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 有了更深的了解。