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