oracle 给定在oracle中的生效日期，如何编写查询以获取最新的员工记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10685632/
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 to write a query to get the latest employee record given the effective date in oracle?
提问by ashishjmeshram
I have a employee and employee_history table. For every update on employee table, I insert a record into the employee_history table.
我有一个员工和员工_历史表。对于员工表的每次更新，我都会在员工历史表中插入一条记录。
Both the tables have a column called as effective date which indicates that the employee status as on that effective date.
这两个表都有一个称为生效日期的列，该列指示该生效日期的员工状态。
Below are my two tables.
下面是我的两张桌子。
I need to get the employee record latest as on that effective date.
我需要在该生效日期获得最新的员工记录。
e.g If I need to get the employee as on 16 may I should get the emp_hist_id = 2 record from history table. As on 5 june I should get the emp_hist_id = 4 from hist table.
例如，如果我需要在 16 日获取员工，我应该从历史记录表中获取 emp_hist_id = 2 记录。在 6 月 5 日，我应该从 hist 表中得到 emp_hist_id = 4。
And as on 15th August I should get the record from employee table itself.
和 8 月 15 日一样，我应该从员工表本身获取记录。
Please help.
请帮忙。
回答by Marco
You could try something like
你可以尝试类似的东西
SELECT * FROM (
SELECT * FROM (
(SELECT emp_id, name, email, title, region, division, "effective date"
FROM employee
WHERE emp_id = desired_id)
UNION
(SELECT emp_id, name, email, title, region, division, "effective date"
FROM employee _history
WHERE emp_id = desired_id)
) t
WHERE t."effective date" <= desired_date
ORDER by t."effective date" DESC) p
WHERE ROWNUM = 1
The idea is to take records related to desired user from both tables, then take dates lower than desired one and finally catch the first
这个想法是从两个表中获取与所需用户相关的记录，然后获取低于所需日期的日期，最后捕获第一个
回答by Gordon Linoff
You can solve this problem without analytic functions. The subquery here calculates the most recent effective date in the history record before the employee effective date:
您可以在没有解析函数的情况下解决这个问题。这里的子查询计算历史记录中员工生效日期之前的最近生效日期：
select e.*
from employee_history eh join
(select e.employee_id, max(eh.effective_date) as latest_effective_date
from employee e join
employee_history eh
on e.employee_id = eh.employee_id and
e.effective_date >= eh.effective_date
) ehl
on eh.employee_id = ehl.employee_id and
eh.effective_date = ehl.effective_date
This solution assumes that there are no duplicate effective dates in the history table. If there are, then you have another option. Assuming the employee_history_ids are assigned sequentially, take the max of that id instead of the date.
此解决方案假定历史记录表中没有重复的生效日期。如果有，那么您还有另一种选择。假设 employee_history_ids 是按顺序分配的，取该 id 的最大值而不是日期。
There are alternative formulations of the solution using Oracle's analytic functions.
有使用 Oracle 分析功能的解决方案的替代公式。
回答by mcha
Try the following :
尝试以下操作：
SELECT *
FROM
(SELECT e.emp_id,
e.name,
e.email,
e.title,
e.region,
e.division,
e.effective_date,
row_number() over (partition BY e.emp_id
ORDER BY e.effective_date DESC) rn
FROM employee e
UNION ALL
SELECT eh.emp_id,
eh.name,
eh.email,
eh.title,
eh.region,
eh.division,
eh.effective_date,
row_number() over (partition BY eh.emp_id
ORDER BY eh.effective_date DESC) rn
FROM employee_history eh)
WHERE effective_date < to_date('15/06/2012','DD/MM/YYYY')
AND rn = 1
you can see a better example here
你可以在这里看到一个更好的例子
