SQL子查询问题，“错误：对FROM子句条目的引用无效...”

问题描述

SQL出现问题（当前使用postgresql）

Having trouble with SQL (currently using postgresql)

我有此查询，因为我需要比较最近的项目和第二个最近的项目：

I have this query as I need to compare the most recent item and the second most recent item:

SELECT p1.*, p2.price_cents FROM "prices" p1
  INNER JOIN
    (
      SELECT price_cents, game_id from prices as p WHERE p.game_id = p1.game_id
        ORDER BY p.created_at DESC LIMIT 1 OFFSET 1
    )
  p2 ON p2.game_id = p1.game_id

这会产生一些错误：

ERROR:  invalid reference to FROM-clause entry for table "p1"
LINE 1: ...AND p.game_id = p1.game_id...
                           ^
HINT:  There is an entry for table "p1", but it cannot be referenced from this part of the query.

我是否有任何原因无法从该子选择中访问p1，是否存在，因为在，p1的数据尚不可用？

Is there any reason I can't access p1 from that subselect, is it a existence issue, as in, p1's data isn't available yet? Is there another way to do this with a JOIN?

推荐答案

尝试这个

SELECT p1.*, (
    SELECT price_cents
    FROM "prices" p
    WHERE p1.game_id = p.game_id
    ORDER BY p.created_at DESC LIMIT 1 OFFSET 1
) as price_cents
FROM "prices" p1

更新根据作者的评论

如果您需要从最近的第二个条目开始输入多个字段，则可以尝试按照以下代码段进行操作

If you need more than one column from second recent entry, you can try following snippet

SELECT * FROM (
    SELECT p.*, (
        SELECT id
        FROM "prices"
        WHERE p.game_id = game_id
        ORDER BY created_at DESC LIMIT 1 OFFSET 1
    ) AS second_id
    FROM "prices" p
) p1 INNER JOIN "prices" p2 ON p1.second_id = p2.id

这篇关于SQL子查询问题，“错误：对FROM子句条目的引用无效...”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！