mysql 行转列的问题

[山城忙碌人]

[code=sql]CREATE TABLE applyProcessPrice
(
pId BIGINT NOT NULL,
pBaseMsgId BIGINT ,
pPrice DOUBLE NOT NULL
)
;


insert into applyProcessPrice (pId,pBaseMsgId,pPrice) values(1,1,200);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice) values(2,1,400);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice) values(3,1,200);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice) values(4,2,400);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice) values(5,2,600);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice) values(6,2,200);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice) values(7,3,880);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice) values(8,3,1100);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice) values(9,3,300);

目前数据

理想数据
pBaseMsgId 价格1 价格2 价格3
1 200 400 600
2 400 600 200
3 880 1100 300


我用这个sql无法实现
select pBaseMsgId,MAX(CASE pid WHEN 1 THEN pprice ELSE 0 END ) as '价格1'
,MAX(CASE pid WHEN 2 THEN pprice ELSE 0 END ) as '价格2' ,
MAX(CASE pid WHEN 3 THEN pprice ELSE 0 END ) as '价格3'
from applyprocessprice group by pBaseMsgId


对mysql不是很熟悉,，求这个sql怎么写

[ACMAIN_CHM]

select pBaseMsgId,
	MAX(CASE pid WHEN 1 THEN pprice ELSE 0 END ) as `价格1`,
	MAX(CASE pid WHEN 2 THEN pprice ELSE 0 END ) as `价格2` ,
	MAX(CASE pid WHEN 3 THEN pprice ELSE 0 END ) as `价格3`
from applyprocessprice  
group by pBaseMsgId

如仍有问题，贴出错误提示。

[山城忙碌人]

引用 1 楼 ACMAIN_CHM 的回复:

select pBaseMsgId,
	MAX(CASE pid WHEN 1 THEN pprice ELSE 0 END ) as `价格1`,
	MAX(CASE pid WHEN 2 THEN pprice ELSE 0 END ) as `价格2` ,
	MAX(CASE pid WHEN 3 THEN pprice ELSE 0 END ) as `价格3`
from applyprocessprice  
group by pBaseMsgId

如仍有问题，贴出错误提示。

谢谢版主你给出的sql,和我的一样，我在问题后面已经留下了，我写的sql，这样无法实现我的需求。我想得到的结果，一个商品对应多个价格，使用sqlservier 2005 语句如下：

select * from (
select row_number() over(partition by pbasemsgid order by pid) as num,pbasemsgid, pprice from applyprocessprice
) as source
pivot (max(pprice) for num in ([1],[2],[3])) as result

[山城忙碌人]

引用 1 楼 ACMAIN_CHM 的回复:

如仍有问题，贴出错误提示。

发现mysql 不支持row_number() over(partiton by ,order by )，我其实很不想改变表结构。目前解决办法如下，不知道版主有没有不改变表结构的方法

CREATE TABLE applyProcessPrice
(
pId BIGINT NOT NULL,
pBaseMsgId BIGINT ,
pPrice DOUBLE NOT NULL ,
pNo INT
)
;

insert into applyProcessPrice (pId,pBaseMsgId,pPrice,pNo) values(1,1,200,1);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice,pNo) values(2,1,400,2);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice,pNo) values(3,1,200,3);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice,pNo) values(4,2,400,1);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice,pNo) values(5,2,600,2);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice,pNo) values(6,2,200,3);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice,pNo) values(7,3,880,1);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice,pNo) values(8,3,1100,2);
insert into applyProcessPrice (pId,pBaseMsgId,pPrice,pNo) values(9,3,300,3); 

select pBaseMsgId,
    MAX(CASE pno WHEN 1 THEN pprice ELSE 0 END ) as `价格1`,
    MAX(CASE pno WHEN 2 THEN pprice ELSE 0 END ) as `价格2` ,
    MAX(CASE pno WHEN 3 THEN pprice ELSE 0 END ) as `价格3`
from applyprocessprice  
group by pBaseMsgId

[ACMAIN_CHM]

select pBaseMsgId, MAX(CASE (select count(*) from applyprocessprice where pBaseMsgId=t.pBaseMsgId and pno<=t.pno) WHEN 1 THEN pprice ELSE 0 END ) as `价格1`, MAX(CASE (select count(*) from applyprocessprice where pBaseMsgId=t.pBaseMsgId and pno<=t.pno) WHEN 2 THEN pprice ELSE 0 END ) as `价格2` , MAX(CASE (select count(*) from applyprocessprice where pBaseMsgId=t.pBaseMsgId and pno<=t.pno) WHEN 3 THEN pprice ELSE 0 END ) as `价格3` from applyprocessprice t group by pBaseMsgId