查了下,似乎MySQL没有类似identity的函数,不过可以通过表的auto_increment类型的字段来模拟实现,如下:create temporary table test( NO int not null auto_increment, NO1 int, NO2 varchar(10), NO3 varchar(4), MM int, primary key (NO)); insert into test(NO1, NO2, NO3, MM) select * from ( select NO1,NO2,'MYA1 ',MYA1 from 表 union all select NO1,NO2,'MYA2 ',MYA2 from 表 union all select NO1,NO2,'MYA3 ',MYA3 from 表 union all select NO1,NO2,'MYA4 ',MYA4 from 表 union all select NO1,NO2,'MYA5 ',MYA5 from 表 union all select NO1,NO2,'MYA6 ',MYA6 from 表) t order by NO1, NO2;注意:union中的表不能为临时表,否则,会报错
SET @NUM=0; CREATE TABLE 新表 AS select @NUM:=@NUM+1 AS NO,NO1,NO2,NO3,MM=MYA1 from( select NO1,NO2,NO3= 'MYA1 ',MYA1 from 表 union all select NO1,NO2,NO3= 'MYA2 ',MYA2 from 表 union all select NO1,NO2,NO3= 'MYA3 ',MYA3 from 表 union all select NO1,NO2,NO3= 'MYA4 ',MYA4 from 表 union all select NO1,NO2,NO3= 'MYA5 ',MYA5 from 表 union all select NO1,NO2,NO3= 'MYA6 ',MYA6 from 表 )a order by NO1,NO2
NO int not null auto_increment,
NO1 int,
NO2 varchar(10),
NO3 varchar(4),
MM int,
primary key (NO));
insert into test(NO1, NO2, NO3, MM)
select *
from (
select NO1,NO2,'MYA1 ',MYA1 from 表
union all
select NO1,NO2,'MYA2 ',MYA2 from 表
union all
select NO1,NO2,'MYA3 ',MYA3 from 表
union all
select NO1,NO2,'MYA4 ',MYA4 from 表
union all
select NO1,NO2,'MYA5 ',MYA5 from 表
union all
select NO1,NO2,'MYA6 ',MYA6 from 表) t
order by NO1, NO2;注意:union中的表不能为临时表,否则,会报错
CREATE TABLE 新表 AS
select @NUM:=@NUM+1 AS NO,NO1,NO2,NO3,MM=MYA1
from(
select NO1,NO2,NO3= 'MYA1 ',MYA1 from 表
union all
select NO1,NO2,NO3= 'MYA2 ',MYA2 from 表
union all
select NO1,NO2,NO3= 'MYA3 ',MYA3 from 表
union all
select NO1,NO2,NO3= 'MYA4 ',MYA4 from 表
union all
select NO1,NO2,NO3= 'MYA5 ',MYA5 from 表
union all
select NO1,NO2,NO3= 'MYA6 ',MYA6 from 表
)a order by NO1,NO2