問題描述

1.有兩個(gè)相同結(jié)構(gòu)的表guangdong_student,shanghai_student,希望聯(lián)合查詢表一，表二，得到表三的結(jié)果。使用union會(huì)導(dǎo)致id重復(fù)，如何做到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的情況下，執(zhí)行下面語句可以做到。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