sql复习笔记 - case when

#case when语法
case column
when <condition> then value
when <condition> then value
....
else value end

#case 搜索函数
case
when<condition> then value
when<condition> then value
else value end

#case行转列
原始表格 consumer
id   date    status
a01  1/1/2019  subscribed
a01  2/1/2020  canceled
a02  2/1/2021  canceled
a03  3/4/2021  canceled
转化表格
id  subscribed canceled
a01  1/1/2019  2/1/2020 
a02  2/1/2021  20/1/2021 
a03  1/4/2021  3/4/2021

方法一：
select id,
max(case status when 'subscribed' then date else 0 end) subscribed
max(case status when 'canceled' then date else 0 end)canceled
from consumer
group by id

方法二：piovt
select * from consumer
pivot(max(date)for status in (subscribed, canceled)

#max也可以用其他聚合方式比如sum min count 选择取代