如何使用 select SQL 语句（Oracle DBMS）生成两个数据集的“合并”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2962254/
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 produce "merge" of two data sets with select SQL statement (Oracle DBMS)?
提问by Roman Kagan
What would be the insert SQL statement to merge data from two tables. For example I have events_source_1 table (columns: event_type_id, event_date) and events_source_2 table (same columns) and events_target table (columns: event_type_id, past_event_date nullalbe, future_event_date nullable).
什么是插入 SQL 语句来合并两个表中的数据。例如，我有 events_source_1 表（列：event_type_id、event_date）和 events_source_2 表（相同列）和 events_target 表（列：event_type_id、past_event_date nullalbe、future_event_date nullable）。
Events_source_1 has past events, Events_source_2 has future events and resultant events_target would contain past and future events in the same row for same event_type_id. If there is no past_events but future_events then past_event_date won't be set and only future_event_date will be and the opposite is true too.
Events_source_1 有过去的事件，Events_source_2 有未来的事件，结果 events_target 将在同一行中包含过去和未来事件的相同 event_type_id。如果没有过去事件但有未来事件，那么过去事件日期不会被设置，只有未来事件日期会被设置，反之亦然。
Thanks a lot in advance for helping me resolving this problem.
非常感谢您帮助我解决这个问题。
回答by scaling_out
Roman, you need to do a full outer join on your select so that, for any given event type id (and I am assuming that you will have 0 or 1 row in both your past and future tables for any particular event type id), you will output a single row, and the appropriate null and non-null dates.
Roman，你需要在你的选择上做一个完整的外连接，这样，对于任何给定的事件类型 id（我假设你在过去和未来的表中都有 0 或 1 行，用于任何特定的事件类型 id），您将输出一行，以及适当的空日期和非空日期。
The first answer will not combine the dates from the two sources into a single row, which is what I believe you asked for.
第一个答案不会将来自两个来源的日期合并为一行，我相信这是您要求的。
The second answer was closer, but would only work for rows with both a past and future date (ie: one in each table) due to the inner join.
第二个答案更接近，但由于内部联接，它仅适用于具有过去和未来日期的行（即：每个表中有一个）。
Example:
例子：
insert into event_target (event_type_id, past_event_date, future_event_date)
select nvl(p.event_type_id, f.event_type_id), p.event_date, f.event_date
from
events_source_1 p full outer join
events_source_2 f on p.event_type_id = f.event_type_id
insert into event_target (event_type_id, past_event_date, future_event_date)
select nvl(p.event_type_id, f.event_type_id), p.event_date, f.event_date
from
events_source_1 p full outer join
events_source_2 f on p.event_type_id = f.event_type_id
Note that the nvl function is for Oracle and will use the first non-null it sees. ISNULL may be used for SQL Server.
请注意， nvl 函数是针对 Oracle 的，将使用它看到的第一个非空值。ISNULL 可用于 SQL Server。
回答by Mark Baker
INSERT INTO events_target
(
SELECT event_type_id,
NULL as past_event_date,
event_date as future_event_date
FROM Events_source_2
UNION
SELECT event_type_id,
event_date as past_event_date,
NULL as future_event_date
FROM Events_source_1
)
As long as event_type_id is unique between Events_source_1 and Events_source_2
只要 event_type_id 在 Events_source_1 和 Events_source_2 之间是唯一的
回答by Andy White
Maybe something like this: (Not 100% sure on the syntax)
也许是这样的：（对语法不是 100% 确定）
insert into event_target (event_type_id, past_event_date, future_event_date)
select
p.event_type_id as event_type_id,
p.event_date as past_event_date,
f.event_date as future_event_date
from events_source_1 p
inner join events_source_2 f on p.event_type_id = f.event_type_id
