锁定老帖子 主题:某牛企一sql面试题目~~~~~~~~
精华帖 (0) :: 良好帖 (0) :: 隐藏帖 (0)
|
|
---|---|
作者 | 正文 |
发表时间:2011-07-01
最后修改:2011-07-01
state 0, 1,2 // 未使用 更新 删除 根据customerid写一条sql (注意是一条)生成表的结构如下: customerid state0 state1 state2 001 11 212 333 002 15 545 3 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |
发表时间: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
最后修改: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
最后修改:2011-07-02
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 |
|
返回顶楼 | |