多行数据分组后, 取每组最大?

需求

经常遇到这样的需求, 一个药品有多个批次, 每个批次都有有效期, 需要取每个药品最先入库的批次.
一个门诊号有多个处方号, 每个处方对应一个诊断, 需要取每个门诊号最后一个处方的诊断.

方案1

可以直接使用SQL:

SELECT DISTINCT  a.DZDSJ,CICDBM,CZDMC,a.CMZH  
FROM ysgzz2022..TBMZBLZDXX2022 a, (SELECT CMZH , MAX(DZDSJ) DZDSJ FROM  ysgzz2022..TBMZBLZDXX2022 GROUP BY CMZH ) b
WHERE a.CMZH = b.CMZH AND a.DZDSJ = b.DZDSJ

方案2

可以使用代码处理:

先把数据读到list中, 再对list处理. 推荐使用stream.

/* 测试类 需要格式化一下
@Data public class Student {     private int id;     private String name;     private String address;     private BigDecimal salary;     private BigDecimal salary2;     private Date birthday;      public Student(int id, String name, String address, BigDecimal salary, BigDecimal salary2) {         this.id = id;         this.name = name;         this.address = address;         this.salary = salary;         this.salary2 = salary2;     }      public Student(int id, String name, String address, BigDecimal salary, BigDecimal salary2, Date birthday) {         this.id = id;         this.name = name;         this.address = address;         this.salary = salary;         this.salary2 = salary2;         this.birthday = birthday;     }      @Override     public String toString() {         return "Student{" + "id=" + id + ", name='" + name + '\'' + ", address='" + address + '\'' + ", salary=" + salary + ", salary2=" + salary2 + ", birthday=" + birthday + '}';     } }
*/

    public void test12_1() {
        List<Student> students = new ArrayList<>();
        students.add(new Student(1, "小一", "天府大道一街", new BigDecimal("1000"), new BigDecimal("1200"), DateUtil.parse("2022-10-01 10:01:01")));
        students.add(new Student(2, "小一", "天府大道一街", new BigDecimal("1000"), new BigDecimal("1200"), DateUtil.parse("2023-10-01 10:01:01")));
        students.add(new Student(3, "小一", "天府大道一街", new BigDecimal("1000"), new BigDecimal("1200"), DateUtil.parse("2021-10-01 10:01:01")));
        students.add(new Student(4, "小一", "天府大道一街", new BigDecimal("1000"), new BigDecimal("1200"), DateUtil.parse("2022-10-01 09:01:01")));
        students.add(new Student(1, "小二", "天府大道一街", new BigDecimal("1000"), new BigDecimal("1200"), null));
        students.add(new Student(2, "小二", "天府大道一街", new BigDecimal("1000"), new BigDecimal("1200"), DateUtil.parse("2023-10-01 10:01:01")));
        students.add(new Student(3, "小二", "天府大道一街", new BigDecimal("1000"), new BigDecimal("1200"), DateUtil.parse("2020-10-01 10:01:01")));
        students.add(new Student(4, "小二", "天府大道一街", new BigDecimal("1000"), new BigDecimal("1200"), DateUtil.parse("2022-10-01 09:01:01")));



        System.out.println("=============================== \n 方式1 ");
        // 先分组
        long startTime = System.currentTimeMillis();
        Map<String, List<Student>> amap = students.stream().collect(Collectors.groupingBy(Student::getName));
        // 再取每组日期最小的
        Map<String, Student> bmap = new HashMap<>();
        for (String name : amap.keySet()) {
            bmap.put(name, amap.get(name).stream().filter(s -> s.getBirthday() != null).min(Comparator.comparing(Student::getBirthday)).get());
        }
        long endTime = System.currentTimeMillis();
        System.out.println(bmap);
        System.out.println("程序运行时间:" + (endTime - startTime) + "ms");



        System.out.println("=============================== \n 方式2 增强for循环 ");
        startTime = System.currentTimeMillis();
        Map<String, List<Student>> cmap = students.stream().collect(Collectors.groupingBy(Student::getName));
        // 分组和方式1一致 取数优化一下 for
        Set<Map.Entry<String, List<Student>>> studentset = cmap.entrySet();
        Map<String, Student> dmap = new HashMap<>();
        for (Map.Entry<String, List<Student>> set : studentset) {
            dmap.put(set.getKey(), set.getValue().stream().filter(s -> s.getBirthday() != null).min(Comparator.comparing(Student::getBirthday)).get());
        }
        endTime = System.currentTimeMillis();
        System.out.println(dmap);
        System.out.println("程序运行时间:" + (endTime - startTime) + "ms");



        System.out.println("=============================== \n 方式3 ");
        startTime = System.currentTimeMillis();
        // 方式3 使用原生api 效率比方式1好点 不如方式2
        Map<String, Student> emap = students.stream().filter(s -> s.getBirthday() != null).
                collect(Collectors.groupingBy(a -> a.getName(),
                        Collectors.collectingAndThen(Collectors.reducing((c1, c2) -> (c1.getBirthday().compareTo(c2.getBirthday()) < 0) ? c1 : c2), Optional::get)));
        System.out.println(emap);
        endTime = System.currentTimeMillis();
        System.out.println("程序运行时间:" + (endTime - startTime) + "ms");

    }

/**
 ===============================
 方式1
 {小二=Student{id=3, name='小二', address='天府大道一街', salary=1000, salary2=1200, birthday=2020-10-01 10:01:01}, 小一=Student{id=3, name='小一', address='天府大道一街', salary=1000, salary2=1200, birthday=2021-10-01 10:01:01}}
 程序运行时间:10ms
 ===============================
 方式2 增强for循环
 {小二=Student{id=3, name='小二', address='天府大道一街', salary=1000, salary2=1200, birthday=2020-10-01 10:01:01}, 小一=Student{id=3, name='小一', address='天府大道一街', salary=1000, salary2=1200, birthday=2021-10-01 10:01:01}}
 程序运行时间:1ms
 ===============================
 方式3
 {小二=Student{id=3, name='小二', address='天府大道一街', salary=1000, salary2=1200, birthday=2020-10-01 10:01:01}, 小一=Student{id=3, name='小一', address='天府大道一街', salary=1000, salary2=1200, birthday=2021-10-01 10:01:01}}
 程序运行时间:3ms
 */