case when用法（行转列）

1. case when条件筛选，实现行转列

学生的成绩表（表名score，列名：学号，课程号，成绩）
sql实现行转列为下面的表结构

答案：

2.case when条件筛选，统计某个分类的个数

计算好评率

select count(1) '总评价数',
sum(case when sat_name='好评' then 1 else 0 end) '好评数',
sum(case when sat_name='好评' then 1 else 0 end)/count(1) as '好评率'
from reddk_userjudge a
join reddk_goodsinfo b
on a.goods_id=b.goods.id
where a.user_name='小张' 
    and sub_time between '2019-01-01' and '2019-03-31'
    and b.goods_name = '母婴'
    and b.brand_name = 'DW'

3. 用户已购买/购买未收藏/收藏未购买/收藏且购买

   select a.user_id,
   a.item_id,
   1 as '已购买',
   (case when fav_time is null then 1 else 0 end)as '购买未收藏',
   0 as '收藏未购买',
   (case when fav_time is not null then 1 else 0 end)as '收藏且购买'
   from redbk_orders a
   left join redbk_favorites b
   on a.user_id=b.user_id and a.itme_id=b.item.id
union 
  select b.user_id,
  b.item_id,
  (case when par_time is not null then 1 else 0 end) as '已购买',
  0 as '购买未收藏',
  (case when par_time is null then 1 else 0 end) as '收藏未购买',
  (case when par_time is not null then 1 else 0 end)as '收藏且购买'
  from redbk_orders a
  right join redbk_favorites b
  on a.user_id=b.user_id and a.itme_id=b.item.id

4.

1.
select sum(case when '单量'<=5 then 1 else 0 end) as '0-5',
sum(case when '单量' between 6 and 10 then 1 else 0 end) as '6-10',
sum(case when '单量' between 11 and 20 then 1 else 0 end) as '11-20',
sum(case when '单量' >=20 then 1 else 0 end) as '20以上'
from (
  select count(distinct order_id) as '单量'
    from table
    where year(t_date)=2020 and month(t_date)=5
    group by user_id
  ) as t
2.输出对应的表格
create table t1 as 
select count(distinct order_id) as '订单数'
from table
where year(t_date)=2020 and month(t_date)=5
group by user_id;
select '0-5' as '单量', sum(case when '订单数'<=5 then 1 else 0 end) as '客户数' from t1 
union
select '6-10' as '单量', sum(case when '订单数' between 6 and 10 then 1 else 0 end) as '客户数' from t1 
union
select '11-20' as '单量', sum(case when '订单数' between 11 and 20 then 1 else 0 end) as '客户数' from t1 
union
select '20 以上' as '单量', sum(case when '订单数' >=20 then 1 else 0 end) as '客户数' from t1