|
发表时间：2011-07-01
有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 |
|
发表时间：2011-07-01
典型的行转列问题
select customid,
count(case status when 0 then status else null end) as status-0,
count(case status when 1 then status else null end) as status-1,
count(case status when 2 then status else null end) as status-2
from custom group by customid.
|
|
发表时间：2011-07-01
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 |
|
发表时间：2011-07-01
liangge0218 写道
典型的行转列问题
select customid,
count(case status when 0 then status else null end) as status-0,
count(case status when 1 then status else null end) as status-1,
count(case status when 2 then status else null end) as status-2
from custom group by customid.
你这种做法，确实可以，我的也行 |
|
发表时间：2011-07-01
liangge0218 写道
典型的行转列问题
select customid,
count(case status when 0 then status else null end) as status-0,
count(case status when 1 then status else null end) as status-1,
count(case status when 2 then status else null end) as status-2
from custom group by customid.
中间部分，我习惯写成sum(case status when 0 then 1 else 0 end) as status-0 感觉灵活性比较好。。。要加权神马的比较方便 |
|
发表时间：2011-07-01
记得我工作面试 全不会做 照样入职
|
|
发表时间：2011-07-01
这个技巧叫行转列，放狗一搜一大堆！
|
|
发表时间：2011-07-01
我一直想知道行转列这种东西，是什么场景下会用到，我从未有机会用！
|
|
发表时间：2011-07-01
BloodyCoder 写道 我一直想知道行转列这种东西，是什么场景下会用到，我从未有机会用！
那你是没有被各式各样的报表折磨过 |
|
发表时间：2011-07-01
select d.customerid as customerid,count(a.state) as state0,count(c.state) as state1,count(c.state) as state2
from report a ,report b,report c,report d where a.customerid = b.customerid and b.customerid = c.customerid and c.customerid = d.customerid and a.state = 0 and b.state = 1 and c.state = 2 group by d.customerid |
