比较两个没有主键的表

Compare two tables without primary keys

提问人:bugrip 提问时间:12/18/2020 最后编辑:Strawberrybugrip 更新时间:12/18/2020 访问量:1527

问:

我有两个没有主键的表。两个表的列数相同,如下所示:

|    Table_1    |     |    Table_2    |
+---+---+---+---+     +---+---+---+---+
| A | B | C | D |     | A | B | C | D |
| 1 | 1 | 1 | 1 |     | 2 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |     | 2 | 2 | 2 | 2 |
+---+---+---+---+     +---+---+---+---+

我想将Table_1中的“A”列与Table_2中的“A”列进行比较,并显示差异。 这是我当前使用的查询:

SELECT t1.A, t1.B, t1.C, t1.D
FROM Table_1 t1
LEFT JOIN Table_2 t2
ON t1.A = t2.A
WHERE t2.A IS NULL
UNION ALL
SELECT t2.A, t2.B, t2.C, t2.D
FROM Table_2 t2
LEFT JOIN Table_1 t1
ON t2.A = t1.A
WHERE t1.A IS NULL

这是我从查询中得到的结果:

|     Result    |
+---+---+---+---+
| A | B | C | D |
+---+---+---+---+
| 1 | 1 | 1 | 1 |
+---+---+---+---+
| 2 | 1 | 1 | 1 |
+---+---+---+---+

我想要的结果是这样的:

|                Result                 |
+----+----+----+----+----+----+----+----+
|t1.A|t2.A|t1.B|t2.B|t1.C|t2.C|t1.D|t2.D|
+----+----+----+----+----+----+----+----+
| 1  | 2  | 1  | 1  | 1  | 1  | 1  | 1  |
+----+----+----+----+----+----+----+----+
MySQL SQL 比较

评论

1赞 Barmar 12/18/2020
您在查询中只选择了 4 列,您期望如何在结果中获得 8 列?
0赞 Barmar 12/18/2020
如果要将它们合并为一行,加入条件是什么?
0赞 Barmar 12/18/2020
在您的示例中,这很简单 - 每个表中只有一行不同的行,您可以将它们连接起来。但是,存在多种差异的更一般情况呢?它们将如何组合以获得您想要的结果。
0赞 bugrip 12/18/2020
有两个没有主键的表。在这两个表中,A 列应具有相同的值。列 B、C 和 D 可能具有不匹配或匹配的值。要求是仅从 A 列中获取不匹配的值,并将它们一起显示在一行中。例如,Table_1 的 A 列是预期结果,Table_2 的 A 列是实际结果。我在上面使用的查询显示与表的差异,但在两行中,目的是将它们放在一行上,就像上面的所需结果一样。
0赞 Barmar 12/18/2020
如果所有行都不匹配怎么办?你怎么知道哪些要配对到同一行的结果中?

答:

0赞 Gordon Linoff 12/18/2020 #1

我将其解释为想要具有不同值的 B/C/D 值。您可以使用:AJOIN

select *
from t1 join
     t2
     on t1.B = t2.B and t1.C = t2.C and t1.D = t2.D
where t1.A <> t2.A;

这至少与您的结果集一致。

编辑:

如果您只想要两列行不同的列,那么我建议:

select max(a1) as a1, max(b1) as b1, max(c1) as c1, max(d1) as d1,
       max(a2) as a2, max(b2) as b2, max(c2) as c2, max(d2) as d2
from ((select a as a1, b as b1, c as c1, d as d1,
              null as a2, null as b2, null as c2, null as d2, 
              row_number() over (order by a) as seqnum
       from table1 t1
       where not exists (select 1
                         from table2 t2
                         where t2.a = t1.a and t2.b = t1.b and t1.c = t2.c and t1.d = t2.d
                        )
      ) union all
      (select null as a1, null as b1, null as c1, null as d1, 
              a, b, c, d,
              row_number() over (order by a) as seqnum
       from table1 t2
       where not exists (select 1
                         from table1 t1
                         where t2.a = t1.a and t2.b = t1.b and t1.c = t2.c and t1.d = t2.d
                        )
      )
     ) t12
group by seqnum;

  

评论

0赞 bugrip 12/18/2020
要求是从“A”列中的table_1和table_2获取不匹配的值,并将它们并排显示在一行中。
0赞 Slava Rozhnev 12/18/2020 #2

如果您使用 MySQL 8.0 或更高版本,则可以使用 ROW_NUMBER() 窗口,如下所示:

select 
    t1.A `t1.A`,
    t1.B `t1.B`,
    t1.C `t1.C`,
    t1.D `t1.D`,
    t2.A `t2.A`,
    t2.B `t2.B`,
    t2.C `t2.C`,
    t2.D `t2.D`
from (
  select 
      *,
      row_number() over() rn
  from Table1
) t1
join (
  select 
      *,
      row_number() over() rn
  from Table2
) t2 on t1.rn = t2.rn;

结果

+======+======+======+======+======+======+======+======+
| t1.A | t1.B | t1.C | t1.D | t2.A | t2.B | t2.C | t2.D |
+======+======+======+======+======+======+======+======+
| 1    | 1    | 1    | 1    | 2    | 1    | 1    | 1    |
+------+------+------+------+------+------+------+------+
| 2    | 2    | 2    | 2    | 2    | 2    | 2    | 2    |
+------+------+------+------+------+------+------+------+

在此处测试 SQL 代码

评论

0赞 The Impaler 12/18/2020
我会在条款中添加。否则,查询将生成一个随机的输出。ORDER BYOVER
0赞 Slava Rozhnev 12/18/2020
我想到顺序,但不知道可用于顺序的字段