如何在jOOQ中的同一个表上编写LEFT OUTER JOIN？

发布于 2024-12-16 17:18:47 字数 251 浏览 3 评论 0原文

如何使用jOOQ编写以下SQL？

SELECT *
FROM food_db_schema.tblCategory AS t1
LEFT OUTER JOIN food_db_schema.tblCategory AS t2 ON t1.category_id = t2.parent_id
WHERE t2.parent_id IS NULL
AND t1.heartbeat = "ALIVE";

数据库是mySQL

原文

how to write following SQL using jOOQ?

SELECT *
FROM food_db_schema.tblCategory AS t1
LEFT OUTER JOIN food_db_schema.tblCategory AS t2 ON t1.category_id = t2.parent_id
WHERE t2.parent_id IS NULL
AND t1.heartbeat = "ALIVE";

database is mySQL

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

祁梦 2024-12-23 17:18:47

flesk 的回答很好地描述了如何使用 jOOQ 1.x 来完成此操作。使用别名的自连接或多或少相当于使用别名的常规连接，如手册中所述：

https://www.jooq.org/doc/latest/manual/sql-building/table-expressions/aliased-tables/

在即将发布的版本中2.0 中，别名将变得不那么冗长并且更加类型安全。因此，flesk 的解决方案可以这样简化：

// Type-safe table aliasing:
TblCategory t1 = TBLCATEGORY.as("t1");
TblCategory t2 = TBLCATEGORY.as("t2");

Record record = create.select()
                      .from(t1)
                       // t1 and t2 give access to aliased fields:
                      .leftOuterJoin(t2).on(t1.CATEGORY_ID.equal(t2.PARENT_ID))
                      .where(t2.PARENT_ID.isNull())
                      .and(t1.HEARTBEAT.equal("ALIVE"));

我还在这里描述了一个更复杂的自连接示例：

http://blog.jooq.org/jooq-meta-a-hard-core-sql-proof-of-concept/

flesk's answer depicts nicely how this can be done with jOOQ 1.x. A self-join using aliasing is more or less equivalent to a regular join using aliasing as described in the manual:

https://www.jooq.org/doc/latest/manual/sql-building/table-expressions/aliased-tables/

In the upcoming version 2.0, aliasing will be made less verbose and more type-safe. Hence flesk's solution could be simplified as such:

// Type-safe table aliasing:
TblCategory t1 = TBLCATEGORY.as("t1");
TblCategory t2 = TBLCATEGORY.as("t2");

Record record = create.select()
                      .from(t1)
                       // t1 and t2 give access to aliased fields:
                      .leftOuterJoin(t2).on(t1.CATEGORY_ID.equal(t2.PARENT_ID))
                      .where(t2.PARENT_ID.isNull())
                      .and(t1.HEARTBEAT.equal("ALIVE"));

I have also described a more complex example for a self-join here:

http://blog.jooq.org/jooq-meta-a-hard-core-sql-proof-of-concept/

回复收藏 0 原文

居里长安 2024-12-23 17:18:47

也许吧

SELECT *
FROM food_db_schema.tblCategory AS t1
WHERE t1.category_id IS NULL
AND t1.heartbeat = "ALIVE";

，但是你确定t2.parent_id既应该是NULL又等于t1.category_id吗？

编辑：

然后

Table<TblCategoryRecord> t1 = TBLCATEGORY.as("t1");
Table<TblCategoryRecord> t2 = TBLCATEGORY.as("t2");

Field<Integer> t1CategoryId = t1.getField(TblCategory.CATEGORY_ID);
Field<String> t1Heartbeat = t1.getField(TblCategory.HEARTBEAT);
Field<Integer> t2ParentId = t2.getField(TblCategory.PARENT_ID);

Record record = create.select().from(t1)
      .leftOuterJoin(t2).on(t1CategoryId.equal(t2ParentId))
      .where(t2ParentId.isNull())
      .and(t1Heartbeat.equal("ALIVE"));

取决于生成的类、属性和元模型对象的名称。

Maybe

SELECT *
FROM food_db_schema.tblCategory AS t1
WHERE t1.category_id IS NULL
AND t1.heartbeat = "ALIVE";

, but are you sure t2.parent_id is both supposed to be NULL and equal to t1.category_id?

EDIT:

Then something like

Table<TblCategoryRecord> t1 = TBLCATEGORY.as("t1");
Table<TblCategoryRecord> t2 = TBLCATEGORY.as("t2");

Field<Integer> t1CategoryId = t1.getField(TblCategory.CATEGORY_ID);
Field<String> t1Heartbeat = t1.getField(TblCategory.HEARTBEAT);
Field<Integer> t2ParentId = t2.getField(TblCategory.PARENT_ID);

Record record = create.select().from(t1)
      .leftOuterJoin(t2).on(t1CategoryId.equal(t2ParentId))
      .where(t2ParentId.isNull())
      .and(t1Heartbeat.equal("ALIVE"));

depending on what the generated classes, properties and meta-model objects are called.

回复收藏 0 原文

~没有更多了~