《学习MySQL语句》——第十六天

# Write your MySQL query statement below

select  round(sum(order_date = customer_pref_delivery_date) * 100 / count(distinct customer_id), 2) as immediate_percentage
from Delivery
where (customer_id, order_date) in 
    (
        select customer_id, min(order_date)
        from Delivery
        group by customer_id
    )


#要变成百分比，因此*100
#联合子查询（,）
#寻找最早的时间，用min()

 

select 
     id
    , sum(case `month` when 'Jan' then revenue else null end) as Jan_Revenue
    , sum(case `month` when 'Feb' then revenue else null end) as Feb_Revenue
    , sum(case `month` when 'Mar' then revenue else null end) as Mar_Revenue
    , sum(case `month` when 'Apr' then revenue else null end) as Apr_Revenue
    , sum(case `month` when 'May' then revenue else null end) as May_Revenue
    , sum(case `month` when 'Jun' then revenue else null end) as Jun_Revenue
    , sum(case `month` when 'Jul' then revenue else null end) as Jul_Revenue
    , sum(case `month` when 'Aug' then revenue else null end) as Aug_Revenue
    , sum(case `month` when 'Sep' then revenue else null end) as Sep_Revenue
    , sum(case `month` when 'Oct' then revenue else null end) as Oct_Revenue
    , sum(case `month` when 'Nov' then revenue else null end) as Nov_Revenue
    , sum(case `month` when 'Dec' then revenue else null end) as Dec_Revenue
from Department 
group by id

#case when then end语句，熟练运用
#sum统计的是当前列的值