英文:
I want to find the difference between 2 tables and the output should be in the expected format
问题
以下是翻译好的部分：
我想要找到两个表之间的差异，输出应该是预期格式。
表AAA(NUM，TXT)
1 One
2 Too
3 Three
4 Four
表BBB(NUM，TXT)
1 One
3
4 Four
2 Two
5 Five
我在Oracle中使用以下查询来获取差异：
select 'only A' where_, only_a.* from (select num, txt from AAA minus select num, txt from BBB) only_a union all
select 'only B' where_, only_b.* from (select num, txt from BBB minus select num, txt from AAA) only_b
输出是
我想要的或预期的输出是
我想要最佳的查询以获得与此问题中所示的期望格式相匹配的输出。
英文:
I want to find the difference between 2 tables and the output should be in the expected format
Table AAA(NUM,TXT)
1 One
2 too
3 Three
4 Four
Table BBB(NUM,TXT)
1 One
3
4 Four
2 Two
5 Five
I am using the below query in oracle to get the differences
select 'only A' where_, only_a.* from (select num, txt from AAA minus select num, txt from BBB) only_a union all
select 'only B' where_, only_b.* from (select num, txt from BBB minus select num, txt from AAA) only_b
OUTPUT is
What I want or Expected Output is
I want best query to get the output in desired format as shown in this question about
答案1
得分: 2
你可以使用单个 FULL OUTER JOIN 来完成这个任务（而不是你的查询，该查询从表中选择 4 次并使用 2 个 MINUS 和一个 UNION ALL）：
SELECT CASE
WHEN a.num IS NOT NULL
THEN 'only A'
ELSE 'only B'
END AS what,
COALESCE(a.num, b.num) AS num,
CASE
WHEN a.num IS NOT NULL
THEN a.txt
ELSE b.txt
END AS txt
FROM aaa a
FULL OUTER JOIN bbb b
ON a.num = b.num AND a.txt = b.txt
WHERE a.num IS NULL
OR b.num IS NULL
ORDER BY num, what
对于样本数据：
CREATE TABLE AAA(NUM,TXT) AS
SELECT 1, 'One' FROM DUAL UNION ALL
SELECT 2, 'too' FROM DUAL UNION ALL
SELECT 3, 'Three' FROM DUAL UNION ALL
SELECT 4, 'Four' FROM DUAL;
CREATE TABLE BBB(NUM,TXT) AS
SELECT 1, 'One' FROM DUAL UNION ALL
SELECT 3, NULL FROM DUAL UNION ALL
SELECT 4, 'Four' FROM DUAL UNION ALL
SELECT 2, 'Two' FROM DUAL UNION ALL
SELECT 5, 'Five' FROM DUAL;
输出：
| WHAT | NUM | TXT |
|---|---|---|
| only A | 2 | too |
| only B | 2 | Two |
| only A | 3 | Three |
| only B | 3 | null |
| only B | 5 | Five |
英文:
You can do it with a single FULL OUTER JOIN (rather than your query which SELECTs from the tables 4 times and uses 2 MINUSes and a UNION ALL):
SELECT CASE
WHEN a.num IS NOT NULL
THEN 'only A'
ELSE 'only B'
END AS what,
COALESCE(a.num, b.num) AS num,
CASE
WHEN a.num IS NOT NULL
THEN a.txt
ELSE b.txt
END AS txt
FROM aaa a
FULL OUTER JOIN bbb b
ON a.num = b.num AND a.txt = b.txt
WHERE a.num IS NULL
OR b.num IS NULL
ORDER BY num, what
Which, for the sample data:
CREATE TABLE AAA(NUM,TXT) AS
SELECT 1, 'One' FROM DUAL UNION ALL
SELECT 2, 'too' FROM DUAL UNION ALL
SELECT 3, 'Three' FROM DUAL UNION ALL
SELECT 4, 'Four' FROM DUAL;
CREATE TABLE BBB(NUM,TXT) AS
SELECT 1, 'One' FROM DUAL UNION ALL
SELECT 3, NULL FROM DUAL UNION ALL
SELECT 4, 'Four' FROM DUAL UNION ALL
SELECT 2, 'Two' FROM DUAL UNION ALL
SELECT 5, 'Five' FROM DUAL;
Outputs:
| WHAT | NUM | TXT |
|---|---|---|
| only A | 2 | too |
| only B | 2 | Two |
| only A | 3 | Three |
| only B | 3 | null |
| only B | 5 | Five |
答案2
得分: 1
Sample data:
SQL> with
2 aaa (num, txt) as
3 (select 1, 'One' from dual union all
4 select 2, 'too' from dual union all
5 select 3, 'Three' from dual union all
6 select 4, 'Four' from dual
7 ),
8 bbb (num, txt) as
9 (select 1, 'One' from dual union all
10 select 2, 'Two' from dual union all
11 select 3, null from dual union all
12 select 4, 'Four' from dual union all
13 select 5, 'Five' from dual
14 )
Query:
15 select 'only A' what, a.* from aaa a minus select 'only A', b.* from bbb b
16 union all
17 select 'only B' what, b.* from bbb b minus select 'only B', a.* from aaa a
18 order by 2, 1;
WHAT NUM TXT
only A 2 too
only B 2 Two
only A 3 Three
only B 3
only B 5 Five
SQL>
英文:
Sample data:
SQL> with
2 aaa (num, txt) as
3 (select 1, 'One' from dual union all
4 select 2, 'too' from dual union all
5 select 3, 'Three' from dual union all
6 select 4, 'Four' from dual
7 ),
8 bbb (num, txt) as
9 (select 1, 'One' from dual union all
10 select 2, 'Two' from dual union all
11 select 3, null from dual union all
12 select 4, 'Four' from dual union all
13 select 5, 'Five' from dual
14 )
Query:
15 select 'only A' what, a.* from aaa a minus select 'only A', b.* from bbb b
16 union all
17 select 'only B' what, b.* from bbb b minus select 'only B', a.* from aaa a
18 order by 2, 1;
WHAT NUM TXT
------ ---------- -----
only A 2 too
only B 2 Two
only A 3 Three
only B 3
only B 5 Five
SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论