MySQL 的 case when 的语法有两种:
- 简单函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
枚举这个字段所有可能的值*
- 搜索函数
CASE WHEN [expr] THEN [result1]…ELSE [default] END
搜索函数可以写判断,并且搜索函数只会返回第一个符合条件的值,其他case
被忽略
select id,
sum(case month when 'Jan' then revenue end) as 'Jan_Revenue',
sum(case month when 'Feb' then revenue end) as 'Feb_Revenue',
sum(case month when 'Mar' then revenue end) as 'Mar_Revenue',
sum(case month when 'Apr' then revenue end) as 'Apr_Revenue',
sum(case month when 'May' then revenue end) as 'May_Revenue',
sum(case month when 'Jun' then revenue end) as 'Jun_Revenue',
sum(case month when 'Jul' then revenue end) as 'Jul_Revenue',
sum(case month when 'Aug' then revenue end) as 'Aug_Revenue',
sum(case month when 'Sep' then revenue end) as 'Sep_Revenue',
sum(case month when 'Oct' then revenue end) as 'Oct_Revenue',
sum(case month when 'Nov' then revenue end) as 'Nov_Revenue',
sum(case month when 'Dec' then revenue end) as 'Dec_Revenue'
from department group by id