mysql - 如何比较两个mysql表？

Question

我是 mysql 的新手。

我有两个完全相同的 mysql 表(它们具有相似的 id)。

表_1

+----+------------+------+
| id | group_name | cell |
+----+------------+------+
|  1 | YE-04      | 12   |
|  2 | AG-11      | -200 |
|  3 | VG-17      | 11   |
+----+------------+------+

表_2

+----+------------+------+
| id | group_name | cell |
+----+------------+------+
|  1 | YE-04      | NULL |
|  2 | AG-10      | 13   |
|  3 | VG-17      | 11   |
+----+------------+------+

我愿意。

首先，将列(参数)与它们的事实进行比较他们之间的不一致。

其次，按 ID 对它们进行分组。

三、以这种形式输出到第三个(新)表(只显示那些不对应的参数):

+----+------------+----------------|----------------+
| id | col_name   | table_1_param  |  table_2_param |
+----+------------+----------------+----------------+
|  1 | cell       | 12             | NULL           |
|  2 | group_name | AG-11          | AG-10          |
|  2 | cell       | -200           | 13             |
+----+------------+----------------+----------------+

我尝试使用 JOIN 和 UNION 命令将它们分组，但它对我不起作用。

Answer 1

您可以使用union all 取消透视，然后聚合:

select id, col, max(val1) as val1, max(val2) as val2
from ((select id, 'group_name' as col, group_name as val1, null as val2
       from table1
      ) union all
      (select id, 'cell' as col, cell as val1, null as val2
       from table1
      ) union all
      (select id, 'group_name' as col, null as val1, group_name as val2
       from table2
      ) union all
      (select id, 'cell' as col, null as val1, cell as val2
       from table2
      ) 
     ) t
group by id, col
having not max(val1) <=> max(val2);