SQL 子查询问题,“错误:对 FROM 子句条目的无效引用..." [英] SQL subquery questions, "ERROR: invalid reference to FROM-clause entry ..."
本文介绍了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 的数据尚不可用?有没有另一种方法可以通过 JOIN 做到这一点?
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 子句条目的无效引用..."的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文