oracle 比较oracle sql中两个不同表中的两列的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33285888/
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
compare two columns from two different tables in oracle sql for differences
提问by ceesharpie
Hi first time posting for SQL,
嗨，第一次为 SQL 发帖，
I need to compare two different columns from two different tables in SQL.
我需要比较 SQL 中两个不同表中的两个不同列。
For example there is two tables and each have one column in them and I need to compare them.
例如，有两个表，每个表中都有一列，我需要比较它们。
Column1 is in table1 and column2 is in table2.
Column1 在 table1 中，column2 在 table2 中。
I need to compare column1 and column2 for differences, I have looked online but was unable to find a query that would be able to help.
我需要比较 column1 和 column2 的差异，我已经在线查看但无法找到能够提供帮助的查询。
Essentially I need to find the inconsistencies in the two columns from two different tables.
本质上，我需要从两个不同的表中找出两列中的不一致之处。
回答by Subin Chalil
You can do inner join and then find the difference.
您可以进行内连接，然后找到差异。
SELECT
table1.column1,
table2.column2,
(CASE WHEN (table1.column1 = table2.column2 THEN 'No change' ELSE 'Change DETECTED' END)) AS Difference
FROM
table1 INNER JOIN table2 ON table1.id=table2.id
回答by tale852150
The following Oracle SQL would be a solution provided you have an idthat you can join on between the two tables.
以下 Oracle SQL 将是一个解决方案，前提id是您可以在两个表之间进行连接。
select tab1.id,
case
when tab1.col1 > tab2.col1 OR tab1.col1 < tab2.col1 then 'Different'
else 'Equals'
end
from tab1, tab2
where tab1.id = tab2.id;
Test data SQL:
测试数据SQL：
create table tab1 ( id number, col1 varchar2(20));
create table tab2 ( id number, col1 varchar2(20));
insert into tab1 values (1, 'ABCD');
insert into tab1 values (2, 'EFGH');
insert into tab1 values (3, 'WXYZ');
insert into tab2 values (1, 'ABCD');
insert into tab2 values (2, 'EFG');
insert into tab2 values (3, 'ZYXW');
commit;
Results:
结果：
ID CASEWHENT
1 Equals
2 Different
3 Different
回答by brenners1302
Try this:
尝试这个：
WITH x AS (SELECT 1 AS ID, 'THE QUICK BROWN' AS tab1 FROM dual UNION ALL
SELECT 2 AS ID, 'FOX JUMPS OVER' AS tab1 FROM dual),
y AS (SELECT 1 AS ID, 'THE QUICK BROWN FOX' AS tab2 FROM DUAL UNION ALL
SELECT 2 AS ID, 'FOX JUMPS OVER' AS TAB2 FROM DUAL)
SELECT X.ID,X.tab1,Y.tab2,(CASE WHEN (X.tab1 = tab2)
THEN 'Tab1 is equal Tab2'
ELSE 'Tab1 is not equal to Tab2' END) AS Remarks
FROM X INNER JOIN Y ON X.ID = Y.ID;
OUTPUT:
输出：
ID TAB1 TAB2 REMARKS
1 THE QUICK BROWN THE QUICK BROWN FOX Tab1 is not equal to Tab2
2 FOX JUMPS OVER FOX JUMPS OVER Tab1 is equal Tab2
