oracle 使用另一个表的最大日期更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2478849/
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
Updating a table with the max date of another table
提问by moleboy
In Oracle 10g, I need to update Table A with data from Table B.
在 Oracle 10g 中，我需要使用表 B 中的数据更新表 A。
Table A has LOCATION, TRANDATE, and STATUS.
表 A 有 LOCATION、TRANDATE 和 STATUS。
Table B has LOCATION, STATUSDATE, and STATUS
表 B 有 LOCATION、STATUSDATE 和 STATUS
I need to update the STATUS column in Table A with the STATUS column from Table B where the STATUSDATE is the max date upto and including the TRANDATE for that LOCATION (basically, I'm getting the status of the location at the time of a particular transaction).
我需要使用表 B 中的 STATUS 列更新表 A 中的 STATUS 列，其中 STATUSDATE 是截至该位置的最大日期并包括该位置的 TRANDATE（基本上，我正在获取该位置在特定时间的状态）交易）。
I have a PL/SQL procedure that will do this but I KNOW there must be a way to get it to work using an analytic, and I've been banging my head too long.
我有一个 PL/SQL 程序可以做到这一点，但我知道必须有一种方法可以使用分析来让它工作，而且我一直在敲我的头太久了。
Thanks!
谢谢！
回答by Vincent Malgrat
this should get you started (Here the MAX function is the aggregate function and not the analytic function):
这应该让你开始（这里的 MAX 函数是聚合函数而不是解析函数）：
UPDATE table_a
SET status = (SELECT MAX(table_b.status)
KEEP (DENSE_RANK FIRST ORDER BY table_b.statusdate DESC)
FROM table_b
WHERE table_a.location = table_b.location
AND table_b.statusdate <= table_a.trandate);
This will update all the rows in table_a, even if there is no prior row in table_b, updating the status to NULL in that case. If you only want to update the rows in table_athat have a corresponding match in table_byou can add a filter:
这将更新 中的所有行table_a，即使 中没有先前的行table_b，在这种情况下将状态更新为 NULL。如果您只想更新table_a具有相应匹配项的行，table_b您可以添加一个过滤器：
UPDATE table_a
SET status = (SELECT MAX(table_b.status)
KEEP (DENSE_RANK FIRST ORDER BY table_b.statusdate DESC)
FROM table_b
WHERE table_a.location = table_b.location
AND table_b.statusdate <= table_a.trandate)
WHERE EXISTS (SELECT NULL
FROM table_b
WHERE table_a.location = table_b.location
AND table_b.statusdate <= table_a.trandate);
回答by Dan
This is a version with the analytic function. It update all the rows in table_a as shown. To update a specific row, add a filter.
这是一个带有解析功能的版本。它更新 table_a 中的所有行，如图所示。要更新特定行，请添加过滤器。
update table_a t1 set status = (
select distinct
first_value(t2.status) over (partition by t1.location, t1.trandate order by t2.statusdate desc)
from temp_b t2
where t1.location = t2.location
and t2.statusdate <= t1.trandate );
