行转列sql 变成临时表_sql技能复习（二）

1.case when函数

结构

CASE

WHEN 条件1 THEN result1

WHEN 条件2 THEN result2

....

[ELSE resultn]

END

case when 函数是基于逻辑判断后进行赋值，即满足其中一个when 条件时，进行then操作，其中，else语句可省略。

应用

1.简单的case when函数

主要是针对某个或某些字段的某些属性进行标注操作，如用户信息表里，对用户基于年龄大小进行少年、青年、中年和老年的划分，再比如，基于年收入曲分KA用户，另外，在产品的销售网络中，基于地理位置划分东西部或者基于销售热度划分等等，都可以用到case when。

2.case when 函数与聚合函数搭配

常见的是case when 函数与聚合函数搭配，即对于新生成的case when 列做某种聚合函数分析，其中一个应用可以是行转列。

行转列示例

1.编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。查询结果格式如下面的示例所示：

Department 表：

+------+---------+-------+

| id | revenue | month |

+------+---------+-------+

| 1 | 8000 | Jan |

| 2 | 9000 | Jan |

| 3 | 10000 | Feb |

| 1 | 7000 | Feb |

| 1 | 6000 | Mar |

+------+---------+-------+

查询得到的结果表：

+------+-------------+-------------+-------------+-----+-------------+

| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |

+------+-------------+-------------+-------------+-----+-------------+

| 1 | 8000 | 7000 | 6000 | ... | null |

| 2 | 9000 | null | null | ... | null |

| 3 | null | 10000 | null | ... | null |

+------+-------------+-------------+-------------+-----+-------------+

题目来源：leetcode

即长表变成宽表。分析：要生成12列存放每个月收入，需建立12个case when表达式，判别条件是month字段是否为对应月的名称，sql语句如下：

select id,
max(case when month='jan' then revenue else null end) as Jan_revenue,
max(case when month='feb' then revenue else null end) as Feb_revenue,
max(case when month='mar' then revenue else null end) as Mar_revenue,
max(case when month='apr' then revenue else null end) as Apr_revenue,
max(case when month='may' then revenue else null end )as May_revenue,
max(case when month='jun' then revenue else null end )as Jun_revenue,
max(case when month='jul' then revenue else null end )as Jul_revenue,
max(case when month='aug' then revenue else null end )as Aug_revenue,
max(case when month='sep' then revenue else null end )as Sep_revenue,
max(case when month='oct' then revenue else null end )as Oct_revenue,
max(case when month='nov' then revenue else null end )as Nov_revenue,
max(case when month='dec' then revenue else null end )as Dec_revenue
from department
group by id

3.case when 新列做为分组字段，对其他字段做聚合分析

结合子查询，略。

case when使用注意：

else可以省略，end不可以省略

多个when 判别条件都成立时，取第一个when 操作

不满足条件时，默认取null值

null值不参与聚合运算

2.if（exp，t_value,f_value）和 ifnull（exp，value）

if（exp，t_value,f_value）中，exp逻辑判别式成立，则取t_value,否则取f_value。

ifnull（exp，value）则用于判别字段exp是否为null值，应用范围窄，针对性强，具体讲，若exp不为null，返回exp，否则返回value。

前者借助 is null 判别式可以转化成后者。两者与case when函数一样，可以简单作为新生成列，也可以和聚合函数搭配使用，或者本身被用作分组字段。

待续。