从列表中的 Oracle 相关子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/611676/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle correlated subquery in FROM list
提问by SarekOfVulcan
I just tried to do a correlated subquery in the FROMclause of a SELECTstatement in Oracle, but I was given an error indicating that I couldn't do the correlation (something to the effect that Obs.pIDwas not recognized).
我只是尝试在 OracleFROM中的SELECT语句的子句中执行相关子查询，但是我收到了一个错误，表明我无法执行相关操作（导致Obs.pID无法识别的结果）。
Should this work?
这应该工作吗？
FROM ml.Person Person
JOIN ml.Obs ON Person.pID = Obs.pId
JOIN (SELECT ObsMax2.pId, ObsMax2.hdId
, MAX(ObsMax2.obsDate) as maxDate
FROM ml.Obs ObsMax2
WHERE ObsMax2.pId = Obs.pId
AND ObsMax2.obsDate < {?EndDate}
GROUP BY ObsMax2.pId, ObsMax2.hdId) ObsMax
ON Obs.pId = ObsMax.pId
AND Obs.hdId = ObsMax.hdId
AND Obs.obsDate = ObsMax.maxDate
My workaround would appear to be to make it a non-correlated subquery, and add criteria to the subquery that keeps it from running completely amuck, amuck, amu--oofSorry.
我的解决办法似乎是使它成为一个非相关子查询，并添加标准，保持它完全运行横行霸道，横行霸道，amu--子查询OOF对不起。
I'd rather figure out how to properly correlate it, though, if possible - the view that works like that subquery takes forever to build.
不过，如果可能的话，我宁愿弄清楚如何正确关联它 - 像该子查询一样工作的视图需要永远构建。
回答by David Aldridge
You can achieve the intent of this part of the query by using an analytic function to identify the maximum obsDate for each pid and hdid.
您可以通过使用解析函数来确定每个 pid 和 hdid 的最大 obsDate 来实现这部分查询的意图。
It would be something like:
它会是这样的：
select ...
from (
SELECT pId,
hdId,
obsDate
MAX(obsDate) over (partition by pId, hdId) maxDate
FROM ml.Obs
WHERE obsDate < {?EndDate}
)
where obsDate = maxDate
/
回答by Shawn Loewen
Sub-queries within a FROM clause cannot refer to other tables from the same FROM clause. Removing the ObsMax2.pId = Obs.pId clause should resolve the problem and from I can tell will give you exactly the same result since the same clause is in the join condition. However, as you mention, you may run into performance issues with having the GROUP BY in the sub-query.
FROM 子句中的子查询不能引用来自同一 FROM 子句的其他表。删除 ObsMax2.pId = Obs.pId 子句应该可以解决这个问题，我可以告诉你会得到完全相同的结果，因为相同的子句在连接条件中。但是，正如您所提到的，在子查询中使用 GROUP BY 可能会遇到性能问题。
From what I can tell, you're trying to get the individual pID/hdId records from ml.Obs with the largest obsDate that's less than {EndDate}. In that case, what about moving the sub-query into the WHERE clause where you can correlate it? E.g.:
据我所知，您正在尝试从 ml.Obs 中获取最大 obsDate 小于 {EndDate} 的单个 pID/hdId 记录。在这种情况下，如何将子查询移动到可以关联它的 WHERE 子句中呢？例如：
select ...
from
ml.Person Person
join ml.Obs on Person.PID = Obs.pId
where Obs.obsDate = (
select max(obsDate)
from ml.Obs Obs2
where Obs2.pId = Obs.pId
and obs2.hdId = Obs.hdId
and Obs2.obsDate < {EndDate})
回答by Alkini
You've prefixed many of your tables with "ml." but not everywhere (the first join, for example). Assuming you need that (for user/permissions/whatever):
您已在许多表前添加了“ml”。但并非无处不在（例如，第一次加入）。假设您需要（对于用户/权限/任何）：
JOIN ml.Obs ON Person.pID = **ml.**Obs.pId
在 Person.pID 上加入 ml.Obs = **ml.**Obs.pId
or
或者
JOIN ml.Obs ObsON Person.pID = Obs.pId
加入 ml.Obs ObsON Person.pID = Obs.pId
There are other places where this would be needed too.
还有其他地方也需要这样做。
If this isn't the case, remove them from your query because they're irrelevant and distracting.
如果不是这种情况，请将它们从您的查询中删除，因为它们不相关且会分散注意力。
