如何比较两个mysql表？

Question

我是mysql的新手。

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

Table_1

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

Table_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);

Answer 2

你可以尝试下面的SQL来完成工作：

SELECT t1.id, t1.group_name, t1.cell AS "table_1_param", t2.cell AS "table_2_param"
FROM Table_1 t1, Table_2 t2'
WHERE t1.id = t2.id
AND t1.cell != t2.cell