如何在JOOQ中创建多列子查询？如何用JOOQ复刻指定SQL查询？

Hey there! Let's break down your two jOOQ questions clearly:

Multi-column subqueries in jOOQ work just like they do in raw SQL — you can either use them as standalone derived tables for joins, or as part of IN/EXISTS conditions with multi-value matches. Here are common scenarios:

Standalone Multi-Column Subquery (Derived Table) #

If you need to fetch aggregated or filtered data across multiple columns to join with your main query, define the subquery with multiple fields and alias it as a table:

// Subquery: Get each user's latest order date and ID
Table<?> latestOrders = select(
        ORDER.USER_ID,
        max(ORDER.CREATED_AT).as("latest_date"),
        max(ORDER.ID).keepDenseRankFirstOrderBy(ORDER.CREATED_AT.desc()).as("latest_order_id")
    )
    .from(ORDER)
    .groupBy(ORDER.USER_ID)
    .asTable("latest_orders");

// Main query: Join with users to get full user + latest order details
Result<?> result = ctx.select(USER.ID, USER.NAME, latestOrders.field("latest_order_id"), latestOrders.field("latest_date"))
    .from(USER)
    .join(latestOrders)
        .on(USER.ID.eq(latestOrders.field(ORDER.USER_ID)))
    .fetch();

Multi-Column IN Subquery #

For filtering rows where a combination of columns matches values from a subquery, use row() to wrap the columns you're matching:

// Find users who have placed orders in both 'NY' and 'CA' regions
Result<UserRecord> result = ctx.selectFrom(USER)
    .where(row(USER.ID).in(
        select(ORDER.USER_ID)
        .from(ORDER)
        .where(ORDER.REGION.eq("NY"))
        .intersect(
            select(ORDER.USER_ID)
            .from(ORDER)
            .where(ORDER.REGION.eq("CA"))
        )
    ))
    // Or for direct multi-column matches:
    // .where(row(USER.ID, USER.REGION).in(select(ORDER.USER_ID, ORDER.REGION).from(ORDER).where(ORDER.TOTAL.gt(1000))))
    .fetch();

Absolutely! jOOQ is built to provide a 1:1 mapping for standard SQL, plus extensive support for vendor-specific dialect features (like PostgreSQL's jsonb operations, MySQL's ON DUPLICATE KEY UPDATE, etc.). As long as your target SQL is valid for your database, you can replicate it exactly with jOOQ's type-safe API.

Example: Replicating a Complex SQL Query #

Suppose your target SQL looks like this:

SELECT
u.id,
u.name,
SUM(o.total) AS total_spent,
ARRAY_AGG(DISTINCT o.product_category) AS purchased_categories
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.signup_date >= '2023-01-01'
GROUP BY u.id, u.name
HAVING SUM(o.total) > 500
ORDER BY total_spent DESC;

The corresponding jOOQ code would be:

Result<?> result = ctx.select(
        USER.ID,
        USER.NAME,
        sum(ORDER.TOTAL).as("total_spent"),
        arrayAggDistinct(ORDER.PRODUCT_CATEGORY).as("purchased_categories")
    )
    .from(USER)
    .join(ORDER).on(USER.ID.eq(ORDER.USER_ID))
    .where(USER.SIGNUP_DATE.ge(Date.valueOf("2023-01-01")))
    .groupBy(USER.ID, USER.NAME)
    .having(sum(ORDER.TOTAL).gt(500))
    .orderBy(field("total_spent").desc())
    .fetch();

If you run into a rare edge case where jOOQ doesn't have a dedicated API, you can always fall back to raw SQL fragments using DSL.sql() to inject custom logic without losing type safety elsewhere.

内容的提问来源于stack exchange，提问作者Mark