有report表:它里面 id name state customerid 四个字段,id为主键 ,customerid为外键
state 1, 2,3 // 未使用 更新 删除
根据customerid写一条sql (注意是一条)生成表的结构如下:
id name state customerid
1 x 1 001
2 x 2 001
3 x 3 001
4 z 1 002
5 z 2 002
6 z 3 002
7 x 2 001
8 x 2 001
9 z 2 002
10 x 1 001
11 z 3 002
查询结果:
customerid state1 state2 state3
002 1 2 2
001 2 3 1
select customerid,
count(case state when '1' then state else null end) as state1,
count(case state when '2' then state else null end) as state2,
count(case state when '3' then state else null end) as state3
from report group by customerid;