比较两个没有主键的表

Question

我有两个表没有主键。两个表的列数相同，它们看起来如下：

|    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 |
+---+---+---+---+     +---+---+---+---+

我想比较列"；A"；从具有列"1"的表1；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  |
+----+----+----+----+----+----+----+----+

Answer 1

如果您使用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代码

Answer 2

我将其解释为希望B/C/D值与不同的A值相同。您可以使用JOIN:

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;