mysql - 如何联合查询两个结构相同的表并且id不重复

问题描述

1.有两个相同结构的表guangdong_student,shanghai_student,希望联合查询表一，表二，得到表三的结果。使用union会导致id重复，如何做到id自增

2.示例：

表一：guangdong_student+----+----------+| id | username |+----+----------+| 1 | jhon || 2 | may || 3 | abcd1111 |+----+----------+表二：shanghai_student+----+----------+| id | username |+----+----------+| 1 | jhon || 2 | mike || 3 | abcd |+----+----------+希望合并后的表：student+----+----------+| id | username |+----+----------+| 1 | jhon || 2 | may || 3 | abcd1111 || 4 | mike || 5 | abcd |+----+----------+

问题解答

在id是Primary key并且为auto_increment的情况下，执行下面语句可以做到。insert into guangdong_student(username) select username from shanghai_student;

set @id=0;select @id := @id+1 id,distinct username, from ((select * from guangdong_student) gd union (select * from shanghai_student) sh) student;回答3：

select @id := @id+1 id, usernamefrom ( select username from guangdong_student union select username from shanghai_student) t1,(SELECT @id:=0) t2