最近面试碰到了一个行列互换的问题,要求如下 :
select 支付渠道,
max(case 订单业务线 when '快车' then 订单金额 else null end) 快车订单金额,
max(case 订单业务线 when '顺风车' then 订单金额 else null end) 顺风车订单金额,
max(case 订单业务线 when '出租车' then 订单金额 else null end) 出租车订单金额
from order_base group by 支付渠道
select 支付渠道,
max(case when 订单业务线='快车' then 订单金额 else null end) 快车订单金额,
max(case when 订单业务线='顺风车' then 订单金额 else null end) 顺风车订单金额,
max(case when 订单业务线='出租车' then 订单金额 else null end) 出租车订单金额
from order_base group by 支付渠道
大致记住:
group by的内容就是第一列的内容,也是要select的第一列内容,后面陆续是第二列,第三列的内容,注意这个顺序
原始表:
select id,
max(case month when 'Jan' then revenue else null end) as Jan_Revenue,
max(case month when 'Feb' then revenue else null end) as Feb_Revenue,
max(case month when 'Mar' then revenue else null end) as Mar_Revenue,
max(case month when 'Apr' then revenue else null end) as Apr_Revenue,
max(case month when 'May' then revenue else null end) as May_Revenue,
max(case month when 'Jun' then revenue else null end) as Jun_Revenue,
max(case month when 'Jul' then revenue else null end) as Jul_Revenue,
max(case month when 'Aug' then revenue else null end) as Aug_Revenue,
max(case month when 'Sep' then revenue else null end) as Sep_Revenue,
max(case month when 'Oct' then revenue else null end) as Oct_Revenue,
max(case month when 'Nov' then revenue else null end) as Nov_Revenue,
max(case month when 'Dec' then revenue else null end) as Dec_Revenue
from Department group by id;
结果表: