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

有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







  

典型的行转列问题

Sql代码  
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.  

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.  

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

liangge0218 写道
典型的行转列问题


Sql代码  
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.  

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.
你这种做法，确实可以，我的也行

liangge0218 写道
典型的行转列问题


Sql代码  
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.  

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
感觉灵活性比较好。。。要加权神马的比较方便

记得我工作面试 全不会做 照样入职

这个技巧叫行转列，放狗一搜一大堆！

我一直想知道行转列这种东西，是什么场景下会用到，我从未有机会用！

BloodyCoder 写道
我一直想知道行转列这种东西，是什么场景下会用到，我从未有机会用！

那你是没有被各式各样的报表折磨过

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

知道了 不错~~~