SQL 识别两个表之间差异的最快方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2293092/
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
Fastest way to identify differences between two tables?
提问by Sonny Boy
I have a need to check a live table against a transactional archive table and I'm unsure of the fastest way to do this...
我需要根据事务存档表检查活动表，但我不确定执行此操作的最快方法...
For instance, let's say my live table is made up of these columns:
例如，假设我的活动表由以下列组成：
- Term
- CRN
- Fee
- Level Code
- 学期
- CRN
- 费用
- 级别代码
My archive table would have the same columns, but also have an archive date so I can see what values the live table had at a given date.
我的存档表将具有相同的列，但也有一个存档日期，因此我可以查看活动表在给定日期具有哪些值。
Now... How would I write a query to ensure that the values for the live table are the same as the most recent entries in the archive table?
现在...我将如何编写查询以确保活动表的值与存档表中的最新条目相同？
PS I'd prefer to handle this in SQL, but PL/SQL is also an option if it's faster.
PS 我更喜欢在 SQL 中处理这个问题，但如果它更快，PL/SQL 也是一种选择。
回答by pierre
SELECT term, crn, fee, level_code
FROM live_data
MINUS
SELECT term, crn, fee, level_code
FROM historical_data
Whats on live but not in historical. Can then union to a reverse of this to get whats in historical but not live.
什么是现场直播，但不是历史直播。然后可以与此相反以获取历史中但不是生活中的内容。
回答by pj.
Simply:
简单地：
SELECT collist
FROM TABLE A
minus
SELECT collist
FROM TABLE B
UNION ALL
SELECT collist
FROM TABLE B
minus
SELECT collist
FROM TABLE A;
回答by Jeffrey Kemp
You didn't mention how rows are uniquely identified, so I've assumed you also have an "id" column:
您没有提到如何唯一标识行，所以我假设您还有一个“id”列：
SELECT *
FROM livetable
WHERE (term, crn, fee, levelcode) NOT IN (
SELECT FIRST_VALUE(term) OVER (ORDER BY archivedate DESC)
,FIRST_VALUE(crn) OVER (ORDER BY archivedate DESC)
,FIRST_VALUE(fee) OVER (ORDER BY archivedate DESC)
,FIRST_VALUE(levelcode) OVER (ORDER BY archivedate DESC)
FROM archivetable
WHERE livetable.id = archivetable.id
);
Note: This query doesn't take NULLS into account - if any of the columns are nullable you can add suitable logic (e.g. NVL each column to some "impossible" value).
注意：此查询不考虑 NULLS - 如果任何列可以为空，您可以添加合适的逻辑（例如，每个列的 NVL 为某个“不可能”值）。
回答by sparkkkey
unload to table.unl select * from table1 order by 1,2,3,4
卸载到 table.unl select * from table1 order by 1,2,3,4
unload to table2.unl select * from table2 order by 1,2,3,4
卸载到 table2.unl select * from table2 order by 1,2,3,4
diff table1.unl table2.unl > diff.unl
diff table1.unl table2.unl > diff.unl
回答by Stephen Denne
Could you use a query of the form:
您可以使用以下形式的查询：
SELECT your columns FROM your live table
EXCEPT
SELECT your columns FROM your archive table WHERE archive date is most recent;
Any results will be rows in your live table that are not in your most recent archive.
任何结果都将是您的实时表中不在您最近存档中的行。
If you also need rows in your most recent archive that are not in your live table, simply reverse the order of the selects, and repeat, or get them all in the same query by performing a (live UNION archive) EXCEPT (live INTERSECTION archive)
如果您还需要最新存档中不在您的活动表中的行，只需颠倒选择的顺序，然后重复，或通过执行 (live UNION archive) EXCEPT (live INTERSECTION archive)
