有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
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
扫一扫
371
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
