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

bravility
  • 239

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     |
+----+----------+
回复
阅读 6.3k
3 个回答

在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;


select @id := @id+1 id, username
from (
    select  username
    from guangdong_student
    union
    select  username
    from shanghai_student
) t1,
(SELECT @id:=0) t2
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
宣传栏