sql以字段重新创建表

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

 

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

                        from report

                        group by  costomerid

select customerid,   
    count(case state when 0 then state else null end) as status_0,   
    count(case state when 1 then state else null end) as status_1,   
    count(case state when 2 then state else null end) as status_2   
    from report group by customerid 

 


 

 

创建表：

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);