PostgreSQL在子查询中引用外部查询

debugcn 发表于 Dev

安德鲁·埃韦特（Andrew Ewert）

我有两个Postgres表（确实是，更多，但为了问题的目的而简化了）-一个记录客户订购的产品的记录，另一个记录每个客户的价格及其生效日期的历史记录。像这样的东西：

“订单”表

customer_id | timestamp           | quantity
------------+---------------------+---------
1           | 2015-09-29 16:01:01 | 5
1           | 2015-10-23 14:33:36 | 3
2           | 2015-10-19 09:43:02 | 7
1           | 2015-11-16 15:08:32 | 2

“价格”表

customer_id | effective_time      | price
------------+---------------------+-------
1           | 2015-01-01 00:00:00 | 15.00
1           | 2015-10-01 00:00:00 | 12.00
2           | 2015-01-01 00:00:00 | 14.00

我正在尝试创建一个查询，该查询将在下订单时返回该客户的每个订单及其单价，如下所示：

理想的结果

customer_id | quantity | price
------------+----------+------
1           | 5        | 15.00
1           | 3        | 12.00
2           | 7        | 14.00
1           | 2        | 12.00

这本质上就是我想要的，但是我知道您不能在内部查询中引用外部查询，而且我在弄清楚如何重构时遇到了麻烦：

SELECT
    o.customer_id,
    o.quantity,
    p.price
FROM orders o
    INNER JOIN (
        SELECT price
        FROM prices x
        WHERE x.customer_id = o.customer_id
            AND x.effective_time <= o.timestamp
        ORDER BY x.effective_time DESC
        LIMIT 1
    ) p
;

谁能建议最好的方法来完成这项工作？

约翰·布林格

prices您可以在SELECT列表中执行子查询，而不是基于表加入内联视图：

SELECT customer_id, quantity, (
    SELECT price
    FROM prices p
    WHERE
      p.customer_id = o.customer_id
        AND p.effective_time <= o.timestamp
    ORDER BY p.effective_time DESC
    LIMIT 1
  ) AS price
FROM orders o

这确实依赖于相关的子查询，这可能会降低性能，但是对于数据的结构方式，我怀疑是否存在更好的选择。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。