如何根据嵌套多重集的值进行排序？

提问者：小点点

如何根据嵌套多重集的值进行排序？

前面的问题介绍了如何使用 jOOQ 的 MULTISET_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();

如果我在最里面的查询中输入ORDERBY，我可以很容易地按薪资对每个部门的员工进行排序。

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

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

将MULTISETs更改为MULTISET_AGGs，并且只在查询的顶层执行ORDER BY，就像前面的问题中只有一个嵌套级别一样，似乎它不能在这里工作，因为您需要同时按两组不同的列进行分组，也因为您不能嵌套聚合函数（至少在PostgreSQL上是这样）。

共1个答案

匿名用户

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

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()
    ));

可以定义一个新的运算符，再次从MULTISET中提取一个值，而不依赖于它的SQL/XML或SQL/JSON序列化实现。或者，您可以使用普通的SQL模板滚动自己的，知道序列化实现是什么样子的（知道它将来可能会发生微妙的变化）