jOOQ加入选择对象

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);

我的问题是，如何从subQ中获取maxCol列，并在join中使用它？我的连接是这样的：

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

我在（）上出错

类型字段中的方法eq（字符串）不适用于参数（字段）

我该怎么办？

Answer 1

这就解决了问题：

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

Answer 2

如果您使用的是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访问权限，这可能运行得更快。

这通常也称为TOP-n查询。在其他数据库中，还有其他方法可以实现这一点，请参阅本文。

如果您从任何表中通过未类型化的名称（String或org.jooq.Name）访问字段，那么编译器没有任何类型信息可以放在生成的字段上

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

从子查询中，提取maxCol字段引用并将其分配给局部变量（假设它是整数类型，如果需要，请替换）：

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)));

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

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

在这种情况下（如果没有不明确的列名称），可以使用该引用从子查询中再次提取列1字段：

select().from(TBL1)
        .join(subQ.asTable())
        .on(TBL1.COL1.eq(subQ.field(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)));