如何在jOOQ中重用一个CTE [英] How to re-use one CTE in another CTE in jOOQ

问题描述

在jOOQ中，以后的CTE中会重新使用CTE。我正在尝试按年份和学校总结学生的完成记录。我正在使用jOOQ 3.11.2和postgres 9.4。

In jOOQ am re-using a CTE in a later CTE. I am trying to summarise student completion records by year and school. I am using jOOQ 3.11.2 and postgres 9.4.

我有正常的SQL代码。但是在jOOQ中，我返回的是空值。

I have working SQL code. However in jOOQ, I am getting null values returned.

这似乎是我在以后的CTE中重用一个CTE的问题。

This appears to be a problem with how I am re-using one CTE in a later CTE.

起初，我认为使用count（）可能是一个问题。从手册看来，count（）的使用正确。作为测试，我删除了查询中对count（）的所有引用，但仍然收到相同的错误。

At first, I thought it might be a problem with the use of count(). From the manual, it looks like count() is being used correctly. As a test, I removed all reference to count() in the query and still get the same error.

我找不到在jOOQ中重用或链接CTE的示例。在SQL中非常简单，如下所示： SQL-使用一个CTE对另一个CTE的引用，但是我在jOOQ中没有掌握它。

I could not find examples of reusing or chaining CTEs in jOOQ. Easy enough in SQL, as shown here: SQL - Use a reference of a CTE to another CTE but I haven't got the hang of it in jOOQ.

在Intellij上以调试模式运行时，我看到一个错误，无法在第二个CTE中评估 select（）语句。

When run in debug mode on Intellij, I see an error that the select() statement cannot be evaluated in the second CTE.

Cannot evaluate org.jooq.impl.SelectImpl.toString()

以下是显示我在做什么的一个最小示例。

Here is a minimal example showing what I am doing.

 CommonTableExpression<Record4<String, String, String, Year>> cteOne = name("CteOne")
    .fields("SCHOOL","STUDENT_NAME", "COURSE_COMPLETED", "YEAR_COMPLETED")
    .as(
            select( a.NAME.as("SCHOOL")
                    , a.STUDENT_NAME
                    , a.COURSE_DESCRIPTION.as("courseCompleted"),
                    , a.YEAR_COMPLETED 
                    )
                    .from(a)
                    .orderBy(a.YEAR_COMPLETED)
    );

CommonTableExpression<Record3<String, Year, Integer >> cteCounts = name("cteCounts")

    .fields("SCHOOL", "YEAR_COMPLETED", "NUM_COMPLETED" )

    .as( with(cteOne)
                    .select(
                            , field(name("cteOne","SCHOOL"), String.class)
                            , field(name("cteOne","YEAR_COMPLETED"), Year.class)
                            , count().as("NUM_COMPS_LOGGED")
                    )
                    .from(cteOne)
                    .groupBy(
                            field(name("cteCompsList","YEAR_COMPLETED"), Year.class)
                          , field(name("cteOne","SCHOOL"), String.class)
                    )
                    .orderBy(
                            field(name("cteCompsList","YEAR_COMPLETED"), Year.class)
                          , field(name("cteOne","SCHOOL"), String.class)
                    )
    );

有人可以为此指出正确的方向吗？

Can someone please point me in the right direction on this?

推荐答案

就像在您的普通SQL查询版本中一样，您的 cteCounts 不应带有 with（cteOne）子句：

Just like in your plain SQL version of your query, your cteCounts should not have a with(cteOne) clause:

WITH 
  cteOne (columns...) AS (select...),
  cteCounts (columns...) AS (select referencing cteOne, no "with cteOne" here...)
SELECT ...
FROM ...

您的查询应该没问题

这篇关于如何在jOOQ中重用一个CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！