java - jOOQ 加入 Select 对象

Question

我想在 SQL 中执行这样的操作:

SELECT 
  * 
FROM 
  tbl1 t1 
  INNER JOIN 
  (SELECT MAX(col1) as maxCol, col2 FROM tbl1 t1 GROUP BY t1.col2) subQ
  ON t1.col2 = subQ.col2
  AND t1.col1 = subQ.maxCol

在 jOOQ 中，我存储了 subQ进入Select<?>对象:

Select<?> subQ = myDSL.select(DSL.max(TBL1.COL1).as("maxCol"), TBL1.COL2)
                      .from(TBL1)
                      .groupBy(TBL1.COL2);

我的问题是，如何获得 maxCol来自 subQ 的专栏并在 join 中使用它？我的join是这样的:

select()
.from(TBL1)
.join(subQ.asTable())
.on(TBL1.COL1.eq(subQ.asTable().field("maxCol")));

我在 .on() 上有错误

The method eq(String) in the type Field is not applicable for the arguments (Field)

我该怎么办？

Answer 1

使用 MySQL 8.0

如果您使用的是 MySQL 8.0+，请不要使用子查询，而是使用窗口函数。这将是与您的查询等效的查询:

SELECT *
FROM (
  SELECT t1.*, RANK() OVER (PARTITION BY t1.col2 ORDER BY col1 DESC) rk
  FROM tbl1 t1
) t
WHERE t.rk = 1

优点是你只有一个tbl1访问，这可能会运行得更快。

This is often also called a TOP-n query. In other databases, there are other ways to implement this, see this article here .

使用旧的 MySQL 版本

如果您从任何表中通过无类型名称(String 或 org.jooq.Name )访问字段，则编译器没有任何类型信息可放入生成的 Field<?> 中，这就是您的原始代码无法编译的原因。

但是，您可以使用以下技术之一:

记原文maxCol现场引用

从您的子查询中，分解出 maxCol字段引用并将其分配给局部变量(假设它是 Integer 类型，如果需要则替换):

Field<Integer> maxCol = DSL.max(TBL1.COL1).as("maxCol");
Select<?> subQ = myDSL.select(maxCol, TBL1.COL2).from(TBL1).groupBy(TBL1.COL2);

现在，您还可以使用此引用从子查询中提取列:

Field<Integer> subQMaxCol = subQ.field(maxCol);

或者直接在您的解决方案中内联:

select().from(TBL1)
        .join(subQ.asTable())
        .on(TBL1.COL1.eq(subQ.field(maxCol)));

命名您的 maxCol TBL1.COL1 之后的列

在这个特定的用例中，不引入任何新名称，而是重新使用 COL1 可能是有意义的作为名字:

Select<?> subQ = myDSL.select(DSL.max(TBL1.COL1).as(TBL1.COL1), TBL1.COL2)
                      .from(TBL1)
                      .groupBy(TBL1.COL2);

在这种情况下(如果没有不明确的 COL1 列名)，您可以使用该引用来提取 COL1再次来自子查询的字段:

select().from(TBL1)
        .join(subQ.asTable())
        .on(TBL1.COL1.eq(subQ.field(TBL1.COL1)));

使用TBL1.COL1引用的数据类型

从您的原始解决方案中，只需在从子查询中提取字段时添加数据类型:

select().from(TBL1)
        .join(subQ.asTable())
        .on(TBL1.COL1.eq(subQ.field("maxCol", TBL1.COL1.getDataType())));

或者，强制它:

select().from(TBL1)
        .join(subQ.asTable())
        .on(TBL1.COL1.eq(subQ.field("maxCol").coerce(TBL1.COL1)));