主页 / dba / 问题 / 238462
Accepted
dotconnor
Asked: 2019-05-18 21:41:48 +0800 CST

返回多个列或 JSON 对象的子查询

  • 772

有什么方法可以让我从子查询中返回多个列，或者至少返回一个列，该列是我需要的所有列的 JSON 对象？

这是一个有效的工作查询：

select r.*,
       (select status
        from builds
        where repo = r.id
          and branch = 'master'
        order by id desc
        limit 1)
from repos r
where id = any($1)

$1id数组在哪里

但我想返回的不仅仅是表中的状态列builds：

select r.*,
       (select status, start_time, end_time
        from builds
        where repo = r.id
          and branch = 'master'
        order by id desc
        limit 1)
from repos r
where id = any($1)

环顾四周后，它似乎row_to_json对我有用。但是，我不确定在这种情况下它应该如何工作：

select r.*,
       row_to_json(select status,
                   start_time, end_time
                   from builds
                   where repo = r.id
                     and branch = 'master'
                   order by id desc
                   limit 1) as build
from repos r
where id = any($1)

我正进入（状态

syntax error at or near "select"
postgresql

1 个回答

  1. Best Answer

    不要让自己的生活变得艰难，使用横向连接：

    SELECT r.*, 
       b.status, b.start_time, b.end_time
FROM repos AS r
   LEFT JOIN LATERAL
      (SELECT status, start_time, end_time
       FROM builds
       WHERE repo = r.id
         AND branch = 'master'
       ORDER BY id DESC
       LIMIT 1
      ) AS b ON TRUE
WHERE id = ANY($1);
    • 4

相关问题