题目中看SQL(二)

1.题目

2. 思考 

#提交的SQL
select 
      t.request_at as Day,
      round(count(case when t.Status != 'completed' then 1 end)/count(t.Status),2)  as 'Cancellation Rate' 
      #round(count(case when t.Status != 'completed' then 1 else null end)/count(t.Status),2)  as 'Cancellation Rate'                                                                  
from trips t
left join users u1  on t.client_id = u1.users_id
left join users u2  on t.driver_id = u2.users_id
where u1.banned = 'no' and u2.banned = 'no' and t.Request_at between '2013-10-01' and '2013-10-03'
group by t.request_at

 总结: 

          a.    对于 case when t.Status != 'completed' then 1 end  不等于1 就直接返回  null，count 不统计null

          b.   也可以用 if(expr1,expr2,expr3)    expr1条件为真，返回expr2,否则返回expr3

           用if可以写成： 

         round(count(if(status != 'completed', status, null)) / count(status), 2)

          c.  也可以用 sum 替换 count , 如下：

# 加上else 0
round(count(case when t.Status != 'completed' then 1 else 0 end)/count(t.Status),2)  as 'Cancellation Rate'