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

发布于 2024-12-03 05:39:21 字数 690 浏览 0 评论 0原文

使用 SQL 时遇到问题（当前使用 postgresql）

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

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 的数据尚不可用？还有另一种方法可以通过 JOIN 来做到这一点吗？

原文

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

This produces a few errors:

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.

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

冰之心 2024-12-10 05:39:21

尝试这个

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

根据作者评论

更新如果您需要最近第二个条目中的多个列，您可以尝试以下代码片段

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

Try this one

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

UPDATE according to authors comment

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

回复收藏 0 原文

心奴独伤 2024-12-10 05:39:21

您需要横向连接：

SELECT p1.*, p2.price_cents FROM "prices" p1 
  INNER JOIN LATERAL
    (
      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

You need a LATERAL JOIN:

SELECT p1.*, p2.price_cents FROM "prices" p1 
  INNER JOIN LATERAL
    (
      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

回复收藏 0 原文

~没有更多了~