我在 Oracle DB 中有两个表
Person (
id
)
Bill (
id,
date,
amount,
person_id
)
如果存在,我需要从最后一张账单中获取人和金额。我试图这样做
SELECT
p.id,
b.amount
FROM Person p
LEFT JOIN Bill b
ON b.person_id = p.id AND b.date = (SELECT MAX(date) FROM Bill WHERE person_id = 1)
WHERE p.id = 1;
但此查询仅适用于 INNER JOIN。在 LEFT JOIN 的情况下,它会抛出ORA-01799 a column may not be outer-joined to a subquery
如何使用左连接从最后一张账单中获得金额?
请尝试以下避免子查询被外部连接
SELECT
p.id,
b.amount
FROM Person p
LEFT JOIN(select * from Bill where date =
(SELECT MAX(date) FROM Bill b1 WHERE person_id = 1)) b ON b.person_id = p.id
WHERE p.id = 1;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句