Oracle SQL - 在选择中选择（在同一个表上！）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3558561/
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 SQL - select within a select (on the same table!)
提问by Nick
I'll try and explain what I'm trying to achieve quickly, since I have no idea how to explain it otherwise!
我会尝试快速解释我想要实现的目标，因为我不知道如何解释它！
We have a table here that shows all employment history for all employees, I want the "Start_Date" of the current post ("Current_Flag" = 'Y'). As well as that, I want the "End_Date" of the post before that (was going to filter by current flag, sort by end date, and just grab the top one)
我们这里有一个表格，显示所有员工的所有就业历史，我想要当前职位的“Start_Date”（“Current_Flag” = 'Y'）。除此之外，我想要在此之前帖子的“End_Date”（将按当前标志过滤，按结束日期排序，然后抓取顶部的）
So anyway, here's my code:
所以无论如何，这是我的代码：
SELECT "Gc_Staff_Number",
"Start_Date",
(SELECT "End_Date"
FROM "Employment_History"
WHERE "Current_Flag" != 'Y'
AND ROWNUM = 1
AND "Employee_Number" = "Employment_History"."Employee_Number"
ORDER BY "End_Date" ASC)
FROM "Employment_History"
WHERE "Current_Flag" = 'Y'
Any suggestions on how to get this working would be fantastic, hopefully the above makes a little bit of sense - to be honest the query at the moment won't even work which really sucks, hmm.
关于如何让这个工作的任何建议都会很棒，希望上面的内容有点意义 - 说实话，目前的查询甚至不起作用，这真的很糟糕，嗯。
(edit: Oh! I'm writing this to query an existing system... which for some reason has all of the stupid double quotes around the table and field names, sigh!)
（编辑：哦！我写这个是为了查询现有系统......出于某种原因，表和字段名称周围有所有愚蠢的双引号，叹息！）
回答by APC
This is precisely the sort of scenario where analytics come to the rescue.
这正是分析能派上用场的那种场景。
Given this test data:
鉴于此测试数据：
SQL> select * from employment_history
2 order by Gc_Staff_Number
3 , start_date
4 /
GC_STAFF_NUMBER START_DAT END_DATE C
--------------- --------- --------- -
1111 16-OCT-09 Y
2222 08-MAR-08 26-MAY-09 N
2222 12-DEC-09 Y
3333 18-MAR-07 08-MAR-08 N
3333 01-JUL-09 21-MAR-09 N
3333 30-JUL-10 Y
6 rows selected.
SQL>
An inline view with an analytic LAG() function provides the right answer:
带有分析 LAG() 函数的内联视图提供了正确的答案：
SQL> select Gc_Staff_Number
2 , start_date
3 , prev_end_date
4 from (
5 select Gc_Staff_Number
6 , start_date
7 , lag (end_date) over (partition by Gc_Staff_Number
8 order by start_date )
9 as prev_end_date
10 , current_flag
11 from employment_history
12 )
13 where current_flag = 'Y'
14 /
GC_STAFF_NUMBER START_DAT PREV_END_
--------------- --------- ---------
1111 16-OCT-09
2222 12-DEC-09 26-MAY-09
3333 30-JUL-10 21-MAR-09
SQL>
The inline view is crucial to getting the right result. Otherwise the filter on CURRENT_FLAG removes the previous rows.
内联视图对于获得正确的结果至关重要。否则 CURRENT_FLAG 上的过滤器会删除前面的行。
回答by El Guapo
I'm a bit confused by the quotes, however, below should work for you:
我对引号有点困惑，但是，以下应该对您有用：
SELECT "Gc_Staff_Number",
"Start_Date", x.end_date
FROM "Employment_History" eh,
(SELECT "End_Date"
FROM "Employment_History"
WHERE "Current_Flag" != 'Y'
AND ROWNUM = 1
AND "Employee_Number" = eh.Employee_Number
ORDER BY "End_Date" ASC) x
WHERE "Current_Flag" = 'Y'
回答by Martin Smith
SELECT eh."Gc_Staff_Number",
eh."Start_Date",
MAX(eh2."End_Date") AS "End_Date"
FROM "Employment_History" eh
LEFT JOIN "Employment_History" eh2
ON eh."Employee_Number" = eh2."Employee_Number" and eh2."Current_Flag" != 'Y'
WHERE eh."Current_Flag" = 'Y'
GROUP BY eh."Gc_Staff_Number",
eh."Start_Date
回答by Patrick Marchand
SELECT "Gc_Staff_Number",
"Start_Date",
(SELECT "End_Date"
FROM "Employment_History"
WHERE "Current_Flag" != 'Y'
AND ROWNUM = 1
AND "Employee_Number" = "Employment_History"."Employee_Number"
ORDER BY "End_Date" ASC)
FROM "Employment_History"
WHERE "Current_Flag" = 'Y'
FYI, the ROWNUM = 1 gets evaluated before the ORDER BY in this case, so that inner query will sort a grand total of (at most) one record.
仅供参考，在这种情况下，在 ORDER BY 之前评估 ROWNUM = 1，因此内部查询将对总计（最多）一条记录进行排序。
If you really are looking for the earliest end_date for a given employee (where current_flag <> 'Y') is this what you're looking for?
如果您真的在寻找给定员工的最早结束日期（其中 current_flag <> 'Y'），这就是您要找的吗？
SELECT "Gc_Staff_Number",
"Start_Date",
eh.end_date
FROM "Employment_History" eh
LEFT OUTER JOIN -- in case the current record is the only record...
(SELECT "Employee_Number"
, MIN("End_Date") as end_date
FROM "Employment_History"
WHERE "Current_Flag" != 'Y'
GROUP BY "Employee_Number"
) emp_end_date
ON eh."Employee_Number" = emp_end_date."Employee_Number"
WHERE eh."Current_Flag" = 'Y'
回答by xoxel
Basically, all you have to do is
基本上，您所要做的就是
select ..., (select ... from ... where ...) as ..., ..., from ... where ...
For exemple. You can insert the (select ... from ... where) wherever you want it will be replaced by the corresponding data.
举个例子。你可以在任何你想要的地方插入 (select ... from ... where) 它将被相应的数据替换。
I know that the others exemple (even if each of them are really great :) ) are a bit complicated to understand for the newbies (like me :p) so i hope this "simple" exemple will help some of you guys :)
我知道其他例子（即使他们每个人都非常棒:)）对于新手来说有点难以理解（比如我:p）所以我希望这个“简单”的例子能帮助你们中的一些人:)
回答by OMG Ponies
This is something I'd use the LAG functionfor:
这是我将LAG 函数用于：
SELECT eh.gc_staff_number,
eh.start_date,
LAG(eh.end_date) OVER (PARTITION BY eh.gc_staff_number
ORDER BY eh.end_date) AS prev_end_date
FROM EMPLOYMENT_HISTORY eh
WHERE eh.current_flag = 'Y'
If you wanted to peek a row ahead, you'd use the LEAD function.
如果您想提前查看一行，您可以使用LEAD 函数。
Compatibility:
兼容性：
To my knowledge, this is supported 9i+ but I haven't confirmed that 8i is supported like the documentation claims.
据我所知，这是支持 9i+ 但我还没有确认支持 8i 就像文档声称的那样。
LEAD and LAG are finally ANSI, but only Oracle and PostgreSQL v8.4+support them currently.
LEAD 和 LAG 最终是 ANSI，但目前只有 Oracle 和PostgreSQL v8.4+支持它们。
