sql以字段重新创建表

有report表：它里面 id name state customerid 四个字段，id为主键 ，customerid为外键
state 0， 1，2 // 未使用 更新 删除
根据customerid写一条sql （注意是一条）生成表的结构如下：
customerid state0 state1 state2
001 11 212 333
002 15 545 3

 

实际上把它看作：select costomerid,count() as status_0,...

                        from report

                        group by  costomerid

 

创建表：

create table report
(
	id int(10) not null auto_increment,
	username VARCHAR(20),
	state int(10),
	customerid int(10),
	PRIMARY KEY(id)
)

insert into report(username,state,customerid) 
VALUES

			('linux1',0,1),
			('linux2',1,1),
			('linux3',2,1),
			('linux4',0,1),
			('linux5',1,1),
			('linux6',2,1),
			('linux7',2,1),
			('linux8',2,1),
			('linux1',0,2),
			('linux2',2,2),
			('linux3',2,2),
			('linux4',2,2),
			('linux5',1,2),
			('linux6',2,2),
			('linux7',2,2),
			('linux8',2,2),
			('linux1',0,3),
			('linux2',1,3),
			('linux3',2,3),
			('linux4',0,3),
			('linux5',1,3),
			('linux6',2,3),
			('linux7',2,3),
			('linux8',2,3),
			('linux1',2,3),
			('linux2',2,3),
			('linux3',2,3),
			('linux4',0,3),
			('linux5',1,3),
			('linux6',2,1),
			('linux7',1,1),
			('linux8',1,1);