oracle from 子句中的子查询如何工作？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18502702/
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
How does a sub query in the from clause work?
提问by Geek
What is a valid use-case for Sub Query in the FROM clause? How does that scheme work? There are many examples of this type in SO. A link to one of those is herebut I can't see how this scheme works.
FROM 子句中子查询的有效用例是什么？该计划如何运作？SO中有很多这种类型的例子。其中一个的链接在这里，但我看不到这个方案是如何工作的。
P.S: If the answer is Oracle specific it is fine.
PS：如果答案是特定于 Oracle 的，那就没问题。
回答by Claude
Here are some use cases for a subquery in the from clause. How it works has been explained in the comments to your question (SQL is mathematical closed thanks to its relational operators).
以下是 from 子句中子查询的一些用例。它的工作原理已在对您的问题的评论中进行了解释（由于其关系运算符，SQL 在数学上是封闭的）。
1. Pivot (SQL Server 2008)
1. 透视（SQL Server 2008）
select P.RUN_ID
, [2012] = sum(P.[2012])
, [2013] = sum(P.[2013])
, [2014] = sum(P.[2014])
, [2015] = sum(P.[2015])
from (select T.RUN_ID
, Y.YEAR
, T.MEASURE
from SOME_TABLE T
inner join
YEAR Y
on T.SOME_ID = Y.SOME_ID
) T
pivot (
sum(MEASURE)
for YEAR in ([2012], [2013], [2014], [2015])
) P
group by
P.RUN_ID
order by
P.RUN_ID
2. over clause (Oracle) based on a union
2.基于联合的over子句（Oracle）
select S.Text_ID
, row_number() over (partition by S.Text_ID order by S.Segmentstart) as Segmentnumber
, S.Segment_ID
, S.Segmentstart
, S.Segmentend
, S.Segmentfragment
from (select S.Text_ID as Text_ID
, S.Satz_ID as Segment_ID
, S.Start as Segmentstart
, S.End as Segmentend
, S.Fragment as Segmentfragment
from Mainclauses S
union all
select X.ID as Text_ID
, null as Segment_ID
, coalesce(S.End, 0) as Segmentstart
, lead(S.Start, 1, X.CONTENT_LENGTH) over (partition by X.ID order by S.Start) as Segmentend
, 'X' as Segmentfragment
from Texts X
left join
Mainclauses S
on X.ID = S.Text_ID
union all
select X.ID as Text_ID
, null as Segment_ID
, 0 as Segmentstart
, min(S.Start) as Segmentend
, 'X' as Segmentfragment
from Texts X
inner join
Mainclauses S
on X.ID = S.Text_ID
group by
X.ID
) S
3. over clause (SQL Server 2008) with join and aggregate
3. over 子句 (SQL Server 2008) with join 和aggregate
select E.X_ID
, Z.SomeThing
, sum(Z.OtherMeasure * E.Measure) as CombinedMeasure
, Sorting = row_number() over
( partition by
E.X_ID
order by
Z.SomeThing
)
from (select E.X_ID
, E.Y_ID
, Measure = sum(E.Measure)
from SomeTable E
group by
E.X_ID
, E.Y_ID
) E
inner join
OtherTable Z
on E.Y_ID = Z.Y_ID
4. Calculate ratio (SQL Server 2008)
4. 计算比率（SQL Server 2008）
with SomeData
( Main_ID
, Sub_ID
, Measure
)
as (select Main_ID
, Sub_ID
, Measure = sum(Measure)
from SomeTable P
group by
Main_ID
, Sub_ID
)
select Main_ID
, Sub_ID
, Ratio = D.Measure / sum(M.Measure) over (partition by M.Main_ID)
from SomeData D
inner join
(select Main_ID
, Measure = sum(Measure)
from SomeData
group by
Main_ID
having sum(Measure) != 0
) M
on M.Main_ID = D.Main_ID
5. Partial Comparision of two (or more) tables (SQL Server 2008)
5. 两个（或多个）表的部分比较（SQL Server 2008）
select *
from (select A, M = sum(M) from S group by A) X
full outer join
(select A, M = sum(M) from T group by A) Y
on X.A = Y.A
where X.A is null
or Y.A is null
or abs(X.M - Y.M) > 0.00000001
Note: These are examples only and in I thought that the subquery in the from clause have been a good solution to achieve the result.
注意：这些只是示例，我认为 from 子句中的子查询是实现结果的一个很好的解决方案。
