JOOQ - 嵌套查询

Question

我正在尝试使用jooq编写此查询。

SELECT region.*, 
       (SELECT COUNT(*)
        FROM city 
        WHERE city.region_id = region.id) AS num
FROM region;

我尝试了几件事情，但都没有成功。到目前为止，我什么也没得到。

dsl.select().from(Tables.REGION).fetch();

我该如何将“num”列添加到我的结果中？感谢您提供的任何帮助。

- user1994514

Answer 1

您的查询将类似于以下内容：

// This is how you can select REGION.*
dsl.select(REGION.fields())

// This is how you can add more fields to the SELECT clause
   .select(

// Nested selects are just select() as well (from DSL)
        select(count())
       .from(CITY)
       .where(CITY.REGION_ID.eq(REGION.ID))

// This is how you convert an org.jooq.Select into a Field
       .asField("num")
   )
   .from(REGION)
   .fetch();

上述实现假定使用静态导入:

import static org.jooq.impl.DSL.*;
import static path.to.generated.Tables.*;

- Lukas Eder