KnexJS如何实现多with子句结合UNION ALL的PostgreSQL查询

KnexJS完全支持你需要的多并列CTE、跨CTE引用、结合UNION ALL的查询逻辑，不需要硬拼接原生SQL，用官方原生链式API即可实现。

核心API用法说明 #

• 定义多个并列的WITH（CTE）子句，只需要链式多次调用.with()方法即可，Knex会自动按调用顺序生成逗号分隔的标准CTE结构，无需额外配置
• 后定义的CTE可以直接引用之前已经声明过的CTE别名，Knex不会做额外的别名拦截，会直接透传到最终生成的SQL中，完全兼容PostgreSQL的TABLE 表名简写语法
• 最终结果集通过.unionAll()方法拼接两个CTE的查询即可，要求两个查询返回的列数、对应列类型完全匹配，和原生SQL的约束一致

对应目标SQL的可落地代码 #

// knex为已完成初始化的PostgreSQL连接实例
const query = knex
  // 定义第一个CTE first
  .with(
    'first',
    knex.select(
      'results.simulation_id',
      'simulation_time',
      'variable',
      'value',
      'runset_id',
      knex.raw("CASE WHEN ended_at IS NULL THEN 'INCOMPLETE' ELSE 'COMPLETE' END AS status")
    )
    .from('results')
    .join('bookkeeping AS bk', 'bk.simulation_id', 'results.simulation_id')
    .where('bk.simulation_id', simulationId) // 绑定第一个入参
    .where('variable', targetVariable) // 绑定第二个入参
    .orderBy('simulation_time')
  )
  // 定义第二个CTE second，内部引用first
  .with(
    'second',
    knex.select(
      'simulation_id',
      knex.raw('null::real AS simulation_time'),
      knex.raw('null AS variable'),
      knex.raw('null::real AS value'),
      knex.raw('null AS runset_id'),
      knex.raw("CASE WHEN ended_at IS NULL THEN 'INCOMPLETE' ELSE 'COMPLETE' END AS status")
    )
    .from('bookkeeping')
    .whereRaw('NOT EXISTS (TABLE first)')
    .where('simulation_id', simulationId) // 绑定第三个入参
  )
  // 拼接UNION ALL逻辑
  .select('*').from('first')
  .unionAll([
    knex.select('*').from('second')
  ]);

// 开发阶段可调用toSQL()校验生成的SQL和参数绑定是否符合预期
// console.log(query.toSQL())
// 执行查询获取结果
const res = await query;

注意事项 #

• 若使用的Knex版本低于0.95，多次链式调用.with()可能出现别名解析异常，升级到0.95及以上稳定版本即可修复
• PostgreSQL特有的null::real类型转换、TABLE first简写语法，需要通过.raw()方法传入，Knex不会自动生成方言专属语法
• 跨数据库迁移时需要注意，NOT EXISTS (TABLE CTE名)是PostgreSQL特有写法，在MySQL等其他数据库中需要替换为标准的NOT EXISTS (SELECT 1 FROM CTE名)写法

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