论坛首页 招聘求职论坛

某牛企一sql面试题目~~~~~~~~

浏览 14929 次
精华帖 (0) :: 良好帖 (0) :: 隐藏帖 (0)
作者 正文
   发表时间:2011-07-01   最后修改: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.
 
1 请登录后投票
   发表时间: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
0 请登录后投票
   发表时间: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.
 

 你这种做法,确实可以,我的也行

0 请登录后投票
   发表时间: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

感觉灵活性比较好。。。要加权神马的比较方便

0 请登录后投票
   发表时间:2011-07-01  
记得我工作面试 全不会做 照样入职
0 请登录后投票
   发表时间:2011-07-01  
这个技巧叫行转列,放狗一搜一大堆!
0 请登录后投票
   发表时间:2011-07-01  
我一直想知道行转列这种东西,是什么场景下会用到,我从未有机会用!
0 请登录后投票
   发表时间:2011-07-01  
BloodyCoder 写道
我一直想知道行转列这种东西,是什么场景下会用到,我从未有机会用!

那你是没有被各式各样的报表折磨过
0 请登录后投票
   发表时间: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
0 请登录后投票
论坛首页 招聘求职版

跳转论坛:
Global site tag (gtag.js) - Google Analytics