Update:
LATERAL
连接允许这样做,并在 Postgres 9.3 中引入。细节:
- LATERAL 和 PostgreSQL 中的子查询有什么区别? https://stackoverflow.com/questions/28550679/what-is-the-difference-between-lateral-and-a-subquery-in-postgresql/28557803#28557803
原因在错误消息中。其中一个元素FROM
列表不能引用另一个元素FROM
列在同一级别。对于同一级别的同伴来说是不可见的。
你可以用一个解决这个问题相关子查询:
SELECT *, (SELECT t FROM rp ORDER BY abs(rp.t - rq.t) LIMIT 1) AS ra
FROM rq
显然,你并不关心来自哪一行RP
你从一组同样接近的行中进行选择,所以我也这样做。
然而,子查询表达式SELECT
列表只能返回one柱子。如果您想要表中的多于一列或所有列RP
,使用类似这样的子查询构造:
我假设存在主键id
在两个表中。
SELECT id, t, (ra).*
FROM (
SELECT *, (SELECT rp FROM rp ORDER BY abs(rp.t - rq.t) LIMIT 1) AS ra
FROM rq
) x;
相关子查询臭名昭著表现不佳。这种查询 - 虽然显然计算你想要的东西 - 将suck特别是,因为表达式rp.t - rq.t
不能使用索引。当表变大时,性能会急剧下降。
这个重写的查询应该能够利用索引于RP.t
,应该执行much大表更快.
WITH x AS (
SELECT *
,(SELECT t
FROM rp
WHERE rp.t < rq.t
ORDER BY rp.t DESC
LIMIT 1) AS t_pre
,(SELECT t
FROM rp
WHERE rp.t >= rq.t
ORDER BY rp.t
LIMIT 1) AS t_post
FROM rq
)
SELECT id, t
,CASE WHEN (t_post - t) < (t - t_pre)
THEN t_post
ELSE COALESCE(t_pre, t_post) END AS ra
FROM x;
再说一次,如果你想要整行:
WITH x AS (
SELECT *
,(SELECT rp
FROM rp
WHERE rp.t < rq.t
ORDER BY rp.t DESC
LIMIT 1) AS t_pre
,(SELECT rp
FROM rp
WHERE rp.t >= rq.t
ORDER BY rp.t
LIMIT 1) AS t_post
FROM rq
), y AS (
SELECT id, t
,CASE WHEN ((t_post).t - t) < (t - (t_pre).t)
THEN t_post
ELSE COALESCE(t_pre, t_post) END AS ra
FROM x
)
SELECT id AS rq_id, t AS rq_t, (ra).*
FROM y
ORDER BY 2;
注意使用复合类型的括号!这里没有多余的括号。手册中有更多相关内容here http://www.postgresql.org/docs/current/interactive/sql-expressions.html#FIELD-SELECTION and here http://www.postgresql.org/docs/current/interactive/rowtypes.html#AEN7249.
使用 PostgreSQL 9.1 进行测试。sqlfiddle 上的演示。 http://sqlfiddle.com/#!1/fdea1/1