我想找到两个表之间的差异，并且输出应该是在期望的格式中。

huangapple
go评论89阅读模式
英文:

I want to find the difference between 2 tables and the output should be in the expected format

问题

以下是翻译好的部分：

我想要找到两个表之间的差异，输出应该是预期格式。

表AAA(NUM，TXT)

  1. 1	One
  2. 2	Too
  3. 3	Three
  4. 4	Four

表BBB(NUM，TXT)

  1. 1	One
  2. 3	
  3. 4	Four
  4. 2	Two
  5. 5	Five

我在Oracle中使用以下查询来获取差异：

  1. select 'only A' where_, only_a.* from (select num, txt from AAA minus select num, txt from BBB) only_a union all
  2. 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. 1	One
  2. 2	too
  3. 3	Three
  4. 4	Four

Table BBB(NUM,TXT)

  1. 1	One
  2. 3	
  3. 4	Four
  4. 2	Two
  5. 5	Five

I am using the below query in oracle to get the differences

  1. select 'only A' where_, only_a.* from (select num, txt from AAA minus select num, txt from BBB) only_a union all
  2. 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）：

  1. SELECT CASE
  2.        WHEN a.num IS NOT NULL
  3.        THEN 'only A'
  4.        ELSE 'only B'
  5.        END AS what,
  6.        COALESCE(a.num, b.num) AS num,
  7.        CASE
  8.        WHEN a.num IS NOT NULL
  9.        THEN a.txt
  10.        ELSE b.txt
  11.        END AS txt
  12. FROM   aaa a
  13.        FULL OUTER JOIN bbb b 
  14.        ON a.num = b.num AND a.txt = b.txt
  15. WHERE  a.num IS NULL
  16. OR     b.num IS NULL
  17. ORDER BY num, what

对于样本数据：

  1. CREATE TABLE AAA(NUM,TXT) AS
  2. SELECT 1,   'One'   FROM DUAL UNION ALL
  3. SELECT 2,   'too'   FROM DUAL UNION ALL
  4. SELECT 3,   'Three' FROM DUAL UNION ALL
  5. SELECT 4,   'Four'  FROM DUAL;
  7. CREATE TABLE BBB(NUM,TXT) AS
  8. SELECT 1,   'One'  FROM DUAL UNION ALL
  9. SELECT 3,   NULL   FROM DUAL UNION ALL
  10. SELECT 4,   'Four' FROM DUAL UNION ALL
  11. SELECT 2,   'Two'  FROM DUAL UNION ALL
  12. 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

fiddle

英文:

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):

  1. SELECT CASE
  2.        WHEN a.num IS NOT NULL
  3.        THEN 'only A'
  4.        ELSE 'only B'
  5.        END AS what,
  6.        COALESCE(a.num, b.num) AS num,
  7.        CASE
  8.        WHEN a.num IS NOT NULL
  9.        THEN a.txt
  10.        ELSE b.txt
  11.        END AS txt
  12. FROM   aaa a
  13.        FULL OUTER JOIN bbb b 
  14.        ON a.num = b.num AND a.txt = b.txt
  15. WHERE  a.num IS NULL
  16. OR     b.num IS NULL
  17. ORDER BY num, what

Which, for the sample data:

  1. CREATE TABLE AAA(NUM,TXT) AS
  2. SELECT 1,   'One'   FROM DUAL UNION ALL
  3. SELECT 2,   'too'   FROM DUAL UNION ALL
  4. SELECT 3,   'Three' FROM DUAL UNION ALL
  5. SELECT 4,   'Four'  FROM DUAL;
  7. CREATE TABLE BBB(NUM,TXT) AS
  8. SELECT 1,   'One'  FROM DUAL UNION ALL
  9. SELECT 3,   NULL   FROM DUAL UNION ALL
  10. SELECT 4,   'Four' FROM DUAL UNION ALL
  11. SELECT 2,   'Two'  FROM DUAL UNION ALL
  12. 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

fiddle

答案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:

  1. SQL> with
  2.   2  aaa (num, txt) as
  3.   3    (select 1, 'One'   from dual union all
  4.   4     select 2, 'too'   from dual union all
  5.   5     select 3, 'Three' from dual union all
  6.   6     select 4, 'Four'  from dual
  7.   7    ),
  8.   8  bbb (num, txt) as
  9.   9    (select 1, 'One'  from dual union all
  10.  10     select 2, 'Two'  from dual union all
  11.  11     select 3, null   from dual union all
  12.  12     select 4, 'Four' from dual union all
  13.  13     select 5, 'Five' from dual
  14.  14    )

Query:

  1.  15  select 'only A' what, a.* from aaa a minus select 'only A', b.* from bbb b
  2.  16  union all
  3.  17  select 'only B' what, b.* from bbb b minus select 'only B', a.* from aaa a
  4.  18  order by 2, 1;
  6. WHAT          NUM TXT
  7. ------ ---------- -----
  8. only A          2 too
  9. only B          2 Two
  10. only A          3 Three
  11. only B          3
  12. only B          5 Five
  14. SQL>

huangapple
  • 本文由 发表于 2023年6月8日 13:51:50
  • 转载请务必保留本文链接：https://go.coder-hub.com/76428932.html
  • oracle
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定