如何按嵌套多重集中的值排序？

An 先前的问题 https://stackoverflow.com/questions/69552492/jooq-multiset-order-result-set-via-conditions-on-multiset涵盖如何使用 jOOQMULTISET_AGG基于嵌套集合上的聚合函数对查询进行排序的功能：使用GROUP BY按父表中所有选定的列对查询进行分组，而不是尝试利用MULTISET.

是否有一种适用于多级集合的方法？我正在使用 PostgreSQL，但显然如果有一种独立于引擎的方法来做到这一点，那就更好了。

例如，如果我有一个使用的嵌套查询MULTISET:

var results =
    create
        .select(
            ORGANIZATIONS.ID,
            ORGANIZATIONS.NAME,
            multiset(
                    select(
                        DEPARTMENTS.ID,
                        DEPARTMENTS.NAME,
                        multiset(
                            select(EMPLOYEES.ID, EMPLOYEES.SALARY, EMPLOYEES.NAME)
                                .from(EMPLOYEES)
                                .where(EMPLOYEES.DEPARTMENT_ID.eq(DEPARTMENTS.ID)))))
                .from(DEPARTMENTS)
                .where(DEPARTMENTS.ORGANIZATION_ID.eq(ORGANIZATIONS.ID)))
        .from(ORGANIZATIONS)
        .fetch();

如果我输入一个，我可以轻松地按薪水对每个部门的员工进行排序ORDER BY在最里面的查询中。

                        multiset(
                            select(EMPLOYEES.ID, EMPLOYEES.SALARY, EMPLOYEES.NAME)
                                .from(EMPLOYEES)
                                .where(EMPLOYEES.DEPARTMENT_ID.eq(DEPARTMENTS.ID))
                                .orderBy(EMPLOYEES.SALARY.desc()))))

但是，我如何按最高员工工资对部门列表进行排序，以及按各部门最高员工工资对组织列表进行排序？

改变MULTISETs into MULTISET_AGGs并且只做ORDER BY在查询的顶层，就像前面的问题中只有一层嵌套一样，似乎它在这里不起作用，因为您需要同时按两组不同的列进行分组，而且还因为您不能嵌套聚合函数（至少在 PostgreSQL 上）。

我想不出一种简单的方法来聚合这些总和而不访问某些表两次。如果我这样做，我会更新这个答案。但这里有一个解决方案可以回答您的问题：

var results = create
    .select(
        ORGANIZATIONS.ID,
        ORGANIZATIONS.NAME,
        multiset(
            select(
                DEPARTMENTS.ID,
                DEPARTMENTS.NAME,

                // Employees per department sorted by salary
                multisetAgg(EMPLOYEES.ID, EMPLOYEES.SALARY, EMPLOYEES.NAME)
                    .orderBy(EMPLOYEES.SALARY.desc()))
           .from(DEPARTMENTS)
           .leftJoin(EMPLOYEES)
               .on(EMPLOYEES.DEPARTMENT_ID.eq(DEPARTMENTS.ID))
           .where(DEPARTMENTS.ORGANIZATION_ID.eq(ORGANIZATIONS.ID))
           .groupBy(DEPARTMENTS.ID, DEPARTMENTS.NAME)

           // Departments sorted by max employee salary
           .orderBy(max(EMPLOYEES.SALARY).desc()))
    .from(ORGANIZATIONS)

    // Organisations sorted by max employee salary via extra join
    .orderBy(field(
        select(max(EMPLOYEES.SALARY))
        .from(EMPLOYEES)
        .where(EMPLOYEES.departments().ORGANIZATION_ID.eq(ORGANIZATION.ID))
    ).desc())
    .fetch();

另一种解决方案是仅在查询后对外部结果进行排序：

var results = create
    .select(
        ORGANIZATIONS.ID,
        ORGANIZATIONS.NAME,
        multiset(
            select(
                DEPARTMENTS.ID,
                DEPARTMENTS.NAME,
                multisetAgg(EMPLOYEES.ID, EMPLOYEES.SALARY, EMPLOYEES.NAME)
                    .orderBy(EMPLOYEES.SALARY.desc()),
                max(EMPLOYEES.SALARY))
           .from(DEPARTMENTS)
           .leftJoin(EMPLOYEES)
               .on(EMPLOYEES.DEPARTMENT_ID.eq(DEPARTMENTS.ID))
           .where(DEPARTMENTS.ORGANIZATION_ID.eq(ORGANIZATIONS.ID))
           .groupBy(DEPARTMENTS.ID, DEPARTMENTS.NAME)
           .orderBy(max(EMPLOYEES.SALARY).desc()))
    .from(ORGANIZATIONS)
    .fetch()
    .sortDesc(Comparator.comparing(
        // r.value3() is the outer multiset()
        r -> r.value3().isEmpty() 
           ? 0 

        // r.value3().get(0).value4() is the top max(EMPLOYEES.SALARY) value
           : r.value3().get(0).value4()
    ));

可以定义一个新的运算符，从 a 中再次提取值MULTISET，而不依赖于其 SQL/XML 或 SQL/JSON 序列化实现。或者，您可以使用自己的纯 SQL 模板 https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating/，知道序列化实现是什么样的（知道它将来可能会发生微妙的变化）