一道行列转换的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 distinct r.customerid,
(select count(r0.id) from report r0 where r.customerid = customerid and r0.state = 0) state0,
(select count(r1.id) from report r1 where r.customerid = customerid and r1.state = 1) state1,
(select count(r2.id) from report r2 where r.customerid = customerid and r2.state = 2) state2
from report r