如何在jOOQ中编写带SUM操作的嵌套SQL查询

用户需要将以下嵌套SQL查询改写为jOOQ代码，且直接改写为非嵌套查询无法得到预期结果：

select organisation_name, sum(api_status = 'COMPLETED') as success_count, sum(api_status <> 'COMPLETED') as failure_count, count(distinct transaction_id) as 'total'
from (
select test_master_table.master_id, test_master_table.transaction_id, test_master_table.api_status, organsiations_table.organisation_name
from test_master_table 
left join organsiations_table on test_master_table.organisation_id = organsiations_table.organisation_id 
left join upload_table on test_master_table.master_id = upload_table.master_id
where (test_master_table.organisation_id = '1' AND (created_date >= current_timestamp()))
group by test_master_table.master_id) as test;

测试用表结构及数据 #

以下是用于复现预期结果的表结构和测试数据：

CREATE TABLE test_master_table (
    master_id INTEGER PRIMARY KEY,
    transaction_id TEXT NOT NULL,
    api_status TEXT,
    organsiation_id INTEGER NOT NULL
);

CREATE TABLE organisation_table (
    organsiation_id INTEGER NOT NULL,
    organisation_name TEXT NOT NULL
);

CREATE TABLE upload_table (
    master_id INTEGER NOT NULL,
    statement_id TEXT PRIMARY KEY,
    file_status TEXT,
    type TEXT
);

-- 插入测试数据
INSERT INTO test_master_table values (1, 'txn-1', 'ERROR', 1);
INSERT INTO test_master_table values(2, 'txn-2', 'ERROR', 1);
INSERT INTO test_master_table values (3, 'txn-3', 'COMPLETED', 1);
INSERT INTO test_master_table values (4, 'txn-4', 'COMPLETED', 1);

INSERT INTO organisation_table values (1,'org-1');
INSERT INTO organisation_table values (2,'org-2');
INSERT INTO organisation_table values (3,'org-3');

INSERT INTO upload_table values (1, 'stmt-1', 'COMPLETED', 'type-1');
INSERT INTO upload_table values (1, 'stmt-2', 'COMPLETED', 'type-1');
INSERT INTO upload_table values (1, 'stmt-3', 'COMPLETED', 'type-1');
INSERT INTO upload_table values (2, 'stmt-4', 'COMPLETED', 'type-1');
INSERT INTO upload_table values (2, 'stmt-5', 'COMPLETED', 'type-1');
INSERT INTO upload_table values (2, 'stmt-6', 'COMPLETED', 'type-1');

预期结果的查询语句 #

正确的嵌套查询（可得到预期结果） #

select organisation_name, sum(api_status = 'COMPLETED') as successCount, sum(api_status <> 'COMPLETED') as failureCount, count(distinct master_id)
from (
select test_master_table.master_id, test_master_table.transaction_id, test_master_table.api_status, organisation_table.organisation_name
from test_master_table
left join organisation_table on test_master_table.organsiation_id = organisation_table.organsiation_id
left join upload_table on test_master_table.master_id = upload_table.master_id
where organisation_table.organsiation_id = '1' and type = 'type-1'
group by test_master_table.master_id
) as test;

非嵌套查询（无法得到预期结果） #

/*
select organisation_table.organisation_name, sum(api_status = 'COMPLETED') as successCount, sum(api_status <> 'COMPLETED') as failureCount, count(distinct test_master_table.master_id)
from test_master_table
left join organisation_table on test_master_table.organsiation_id = organisation_table.organsiation_id
left join upload_table on test_master_table.master_id = upload_table.master_id
where organisation_table.organsiation_id = '1' and type = 'type-1'
group by test_master_table.master_id;
*/

jOOQ实现代码 #

对应上述嵌套SQL的jOOQ代码如下（假设已通过jOOQ代码生成器生成了表对象）：

// 构建子查询
Table<?> subquery = select(
        TEST_MASTER_TABLE.MASTER_ID,
        TEST_MASTER_TABLE.TRANSACTION_ID,
        TEST_MASTER_TABLE.API_STATUS,
        ORGANISATION_TABLE.ORGANISATION_NAME
    )
    .from(TEST_MASTER_TABLE)
    .leftJoin(ORGANISATION_TABLE)
        .on(TEST_MASTER_TABLE.ORGANSIATION_ID.eq(ORGANISATION_TABLE.ORGANSIATION_ID))
    .leftJoin(UPLOAD_TABLE)
        .on(TEST_MASTER_TABLE.MASTER_ID.eq(UPLOAD_TABLE.MASTER_ID))
    .where(ORGANISATION_TABLE.ORGANSIATION_ID.eq("1")
        .and(UPLOAD_TABLE.TYPE.eq("type-1")))
    .groupBy(TEST_MASTER_TABLE.MASTER_ID)
    .asTable("test");

// 外层查询
ResultQuery<Record> resultQuery = select(
        subquery.field(ORGANISATION_TABLE.ORGANISATION_NAME),
        sum(when(subquery.field(TEST_MASTER_TABLE.API_STATUS).eq("COMPLETED"), 1).otherwise(0)).as("successCount"),
        sum(when(subquery.field(TEST_MASTER_TABLE.API_STATUS).ne("COMPLETED"), 1).otherwise(0)).as("failureCount"),
        countDistinct(subquery.field(TEST_MASTER_TABLE.MASTER_ID))
    )
    .from(subquery);

// 执行查询并获取结果
Result<Record> result = resultQuery.fetch();

说明 #

• 先通过select(...).asTable("test")构建子查询，与原始SQL的内层查询逻辑完全对应
• 外层查询使用when(...).otherwise(0)替代SQL中的sum(api_status = 'COMPLETED')，确保在不同数据库中行为一致（部分数据库不支持直接将布尔值转为整数求和）
• 所有表字段均使用jOOQ生成器生成的常量，避免硬编码表名和字段名

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