java - 如何从一个字段的结果集中选择单行？

Question

我的选择查询是:

sql.select(COMPANY.NAME, COMPANY_CONTACT.NAME.as("CONTACT_INTERACTION"))
                .from(COMPANY)
                .join(COMPANY_CONTACT)
                    .on(COMPANY_CONTACT.COMPANY_ID.equal(COMPANY.ID))
.where(COMPANY.DELETED.equal(false));

“sql”的类型为DSLContext。

我正在尝试从结果集中的所有对象中仅选择一个 COMPANY_CONTACT.NAME。

Answer 1

这就是我所说的 TOP N per category query ，其中 N = 1。

使用 SQL 标准 LATERAL

使用 SQL 的最简单解决方案是在 PostgreSQL、Oracle、DB2 中使用 SQL 标准 LATERAL，或者在 SQL Server、Oracle 中使用 APPLY。这是 PostgreSQL 版本:

SELECT
  c.name,
  cc.name AS contact_interaction
FROM company AS c
CROSS JOIN LATERAL (
  SELECT cc.name
  FROM company_contact cc
  WHERE cc.company_id = c.id
  ORDER BY cc.follow_up DESC
  LIMIT 1
) AS cc 
WHERE NOT c.deleted

或者使用 jOOQ:

sql.select(COMPANY.NAME, COMPANY_CONTACT.NAME.as("CONTACT_INTERACTION"))
   .from(COMPANY)
   .crossJoin(lateral(table(
        select(COMPANY_CONTACT.NAME)
       .from(COMPANY_CONTACT)
       .where(COMPANY_CONTACT.COMPANY_ID.eq(COMPANY.ID))
       .orderBy(COMPANY_CONTACT.FOLLOW_UP.desc())
       .limit(1)
   ).as(COMPANY_CONTACT)))
   .where(not(COMPANY.DELETED))
   .fetch();

使用 PostgreSQL 特定的 DISTINCT ON

在 PostgreSQL 中，也可以使用 DISTINCT ON 编写每个类别的 TOP 1 查询，这是针对 PostgreSQL 的供应商特定的，并且也受 jOOQ 支持:

SELECT DISTINCT ON (c.company_id) c.name, cc.name AS contact_interaction
FROM company AS c
JOIN company_contact AS cc
ON cc.company_id = c.id
WHERE NOT c.deleted
ORDER BY c.company_id, cc.follow_up DESC

或者在 jOOQ 中

sql.selectDistinct(COMPANY.NAME, COMPANY_CONTACT.NAME.as("CONTACT_INTERACTION"))
   .on(COMPANY.COMPANY_ID)
   .from(COMPANY)
   .join(COMPANY_CONTACT)
   .on(COMPANY_CONTACT.COMPANY_ID.eq(COMPANY.ID))
   .where(not(COMPANY.DELETED))
   .orderBy(COMPANY.COMPANY_ID, COMPANY_CONTACT.FOLLOW_UP.desc())
   .fetch();

使用窗口函数

同样的功能也可以使用窗口函数来实现，但它会不如上述解决方案简单。但是，如果您希望在结果中获得 TOP 1 联系人，那么在 PostgreSQL 中使用 RANK() 函数是不可避免的。

A solution using window functions is also discussed in the previously linked blog post

注意事项

上面所有的 jOOQ 代码都假设你有这个静态导入:

import static org.jooq.impl.DSL.*;