在 Oracle 中执行表同步报告的优雅方法?
因此,我有两个表
TABLE_A
KEYA | VALUE
1 | 1.2
2 | 2.3
3 | 8.3
TABLE_B
KEYB | VALUE
1 | 1.2
3 | 1.6
4 | 5.5
我想创建这两个表之间同步状态的报告。即为每个记录生成以下信息:
- 记录 X 位于 TABLE_A 中,但不在 TABLE_B 中
- 记录 X 位于 TABLE_B 中,但不在 TABLE_A 中
- 记录 X 存在于两个表中,但 VALUE 不同
- (同步的记录不会)没有出现在报告中)
我真的不需要文本(实际上,我不想要它)。可能只是值本身:
[Table_A.KEYA (if present)]|[Table_A.Value (if present)]|[Table_B.KEYB (if present)]|[Table_B.Value (if present)]
给定的示例表应该产生:
|2|2.3| | |
|3|8.3|3|1.6|
| | |4|5.5|
我目前正在使用长系列的连接和减法来做到这一点,但我认为这对于数据库来说应该很常见,并且 Oracle 可能有一个更优雅的(并且可能更多)有效)的做法。有人可以拍摄一些提示吗?
谢谢一百万!
f.
So I have my two tables
TABLE_A
KEYA | VALUE
1 | 1.2
2 | 2.3
3 | 8.3
TABLE_B
KEYB | VALUE
1 | 1.2
3 | 1.6
4 | 5.5
And I want to create a report of the sync status between these two tables. I.e. Generate for each record the following information:
- Record X is in TABLE_A but not in TABLE_B
- Record X is in TABLE_B but not in TABLE_A
- Record X is present in both tables but differs in VALUE
- (records synced won't appear in the report)
I don't really need the text (actually, I don't want it). Could be just the values itself:
[Table_A.KEYA (if present)]|[Table_A.Value (if present)]|[Table_B.KEYB (if present)]|[Table_B.Value (if present)]
The given example tables should yield:
|2|2.3| | |
|3|8.3|3|1.6|
| | |4|5.5|
I'm currently doing that with longs series of joins and minuses all around, but figured that should be quite common for DBs and Oracle possibly have a more elegant (and possibly more efficient) way of doing it. Could anyone shoot some tips?
Thanks a mil!
f.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我认为连接和减法都很好:-)
上面的例子会给出类似的东西,
它很快就会因为很多列而变得混乱。
I think joins and minuses are fine :-)
The above example would give something like
It soon gets messy with lots of columns.