mysql 列变成行转换怎么写？(列行转换)

[noamd_moon]

一段sqlserver sql代码完成行列转换，但在mysql里无法执行，

请问mysql的应该怎么写？

create table A1(NO1 int,NO2 varchar(10),MYA1 int,MYA2 int,MYA3 int,MYA4 int,MYA5 int,MYA6 int)
insert A1 select 1, 'ABC ',10,20,25,30,19,80
union all select 15, 'BCS ',7, 15,17,23,50,61
go


select NO=identity(int,1,1),NO1,NO2,NO3,MM=MYA1
into 新表
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

select * from 新表
go
问题补充：另外在如果已经有表的话 ， sqlserver 是 insert into ...select ....

请问mysql应该怎么写能? 谢谢。 项目急需，用于做ecshop导入，ecshop的商品属性太麻烦。。。。亏我们买的商业版。。。。。

[ACMAIN_CHM]

可以参考下贴中的多种方法。

http://blog.csdn.net/ACMAIN_CHM/archive/2009/06/19/4283943.aspx

[xinghun61]

查了下，似乎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中的表不能为临时表，否则，会报错

[noamd_moon]

感谢诸位,2楼方法已经成功~

[WWWWA]

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