dotconnor's questions

假设我们有这样的表设置：

+---------+--------------+---------------+
| id text | time integer | value decimal |
+---------+--------------+---------------+
|       1 |            1 |            10 |
|       1 |            2 |            10 |
|       1 |            3 |             7 |
|       1 |            4 |             8 |
|       1 |            5 |             6 |
|       1 |            6 |             5 |
|       1 |            7 |             4 |
|       1 |            8 |             3 |
|       1 |            9 |             7 |
|       1 |           10 |            11 |
|       1 |           11 |             3 |
|       1 |           12 |             8 |
|       1 |           13 |             2 |
|       1 |           14 |             4 |
|       1 |           15 |             1 |
+---------+--------------+---------------+

是否可以在id一定时间内选择相同条件下的开始时间和结束时间？假设我们想查看1值所在的ID< 10至少几秒钟。所以结果查询将返回：

+------------+----------+
| start_time | end_time |
+------------+----------+
|         11 |       15 |
|          3 |        9 |
+------------+----------+

有什么方法可以让我从子查询中返回多个列，或者至少返回一个列，该列是我需要的所有列的 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"