QueryDSL转换含WITH子句与UNION的Oracle SQL报错求助

问题背景 #

在将包含WITH子句、UNION ALL和动态过滤条件的Oracle SQL转换为QueryDSL 5.0.0代码时，运行抛出异常：

java.lang.IllegalArgumentException: 不要混合使用union和from

目标SQL需基于Students表生成多维度聚合结果（age、gender、mainCity各取top5数据），且支持用户传入的动态过滤条件（如score>20、mainCity IN ('LA','NYC') AND score BETWEEN 50 AND 60）。

异常原因 #

原代码中每个聚合分支（age、gender、mainCity）都单独创建了带WITH子句的查询，再直接调用unionAll合并。QueryDSL的union操作不支持每个分支查询都包含独立的with子句，这种结构会被判定为非法的混合使用union和from操作。

解决方案 #

将WITH子句提升到整个union查询的外层，同时为每个聚合分支添加rownum过滤逻辑，对齐目标SQL的结构：

修正后完整代码 #

private final QStudent table = QStudent.student;
private final QAggregationValues aggregationValues = QAggregationValues.aggregationValues;

public List<AggregationResultRow> getAggregations(AggregationRequest aggregationRequest) {
    // 构建WITH子句中的filtered_values数据集
    SubQueryExpression<AggregationValues> filteredValues = new BlazeJPAQuery<>(entityManager, criteriaBuilderFactory)
            .select(Projections.constructor(AggregationValues.class,
                    table.age,
                    table.gender,
                    table.mainCity))
            .from(table)
            .where(getConditions(aggregationRequest));

    // 构建age维度聚合子查询（含分组排序+top5过滤）
    SubQueryExpression<AggregationResultRow> ageSubQuery = new BlazeJPAQuery<>(entityManager, criteriaBuilderFactory)
            .select(Projections.constructor(AggregationResultRow.class,
                    aggregationValues.age.as("value"),
                    aggregationValues.count().as("row_count"),
                    Expressions.asString("age").as("column_name")))
            .from(aggregationValues)
            .groupBy(aggregationValues.age)
            .orderBy(aggregationValues.count().desc())
            .limit(5);

    // 构建gender维度聚合子查询
    SubQueryExpression<AggregationResultRow> genderSubQuery = new BlazeJPAQuery<>(entityManager, criteriaBuilderFactory)
            .select(Projections.constructor(AggregationResultRow.class,
                    aggregationValues.gender.as("value"),
                    aggregationValues.count().as("row_count"),
                    Expressions.asString("gender").as("column_name")))
            .from(aggregationValues)
            .groupBy(aggregationValues.gender)
            .orderBy(aggregationValues.count().desc())
            .limit(5);

    // 构建mainCity维度聚合子查询
    SubQueryExpression<AggregationResultRow> mainCitySubQuery = new BlazeJPAQuery<>(entityManager, criteriaBuilderFactory)
            .select(Projections.constructor(AggregationResultRow.class,
                    aggregationValues.mainCity.as("value"),
                    aggregationValues.count().as("row_count"),
                    Expressions.asString("main_city").as("column_name")))
            .from(aggregationValues)
            .groupBy(aggregationValues.mainCity)
            .orderBy(aggregationValues.count().desc())
            .limit(5);

    // 外层主查询：声明WITH子句，合并三个子查询并执行
    return new BlazeJPAQuery<>(entityManager, criteriaBuilderFactory)
            .with(aggregationValues, filteredValues)
            .selectFrom(ageSubQuery.unionAll(genderSubQuery, mainCitySubQuery))
            .fetch();
}

关键修改说明 #

• 统一WITH子句作用域：将WITH子句移到最外层主查询，确保filtered_values数据集在所有聚合分支中共享，避免分支重复声明with导致的语法冲突
• top5过滤实现：使用QueryDSL的limit(5)替代手动处理rownum，框架会自动转换为Oracle的rownum <=5逻辑，同时添加orderBy保证取数的排序规则
• 合规的union合并方式：将三个聚合查询转为子查询，通过unionAll合并后作为主查询的数据源，符合QueryDSL对union操作的语法要求

内容的提问来源于stack exchange，提问作者NBS