【Mysql】日期、行变列（IF、CASE WHEN THEN）语句总结

1. 获取昨天的日期
   日期相减函数 datediff

select datediff(curdate(),INTERVAL 1 DAY) ;

2. 删除Person表中重复的Email数据，只保留id最小的一条

delete p1 from Person p1,Person p2 where p1.Email = p2.Email and p1.Id > p2.Id;

3. 换座位，1与2换，3与4换，5保留原来的
   COALESCE空值表达式，第一个不为空，返回第一个结束；否则继续判断接下来的数据；

select id, COALESCE(s2.student,s1.student) from seat s1, LEFT JOIN
    seat s2 ON ((s1.id + 1) ^ 1) - 1 = s2.id
ORDER BY s1.id;

4. 交换性别，‘f’ 变 ‘m’, ‘m’ 变 ‘f’

update salary set sex= 
   case sex when 'f' then 'm' 
       else 'f'
       END;

5. 月份列变行，求总和，并汇总
   if(month=‘Jan’,revenue,null) Jan_Revenue
   case month when ‘Jan’ then revenue END Jan_Revenue

select id,
sum(if(month='Jan',revenue,null)) Jan_Revenue,
sum(if(month='Feb',revenue,null)) Feb_Revenue,
sum(if(month='Mar',revenue,null)) Mar_Revenue,
sum(if(month='Apr',revenue,null)) Apr_Revenue,
sum(if(month='May',revenue,null)) May_Revenue,
sum(if(month='Jun',revenue,null)) Jun_Revenue,
sum(if(month='Jul',revenue,null)) Jul_Revenue,
sum(if(month='Aug',revenue,null)) Aug_Revenue,
sum(if(month='Sep',revenue,null)) Sep_Revenue,
sum(if(month='Oct',revenue,null)) Oct_Revenue,
sum(if(month='Nov',revenue,null)) Nov_Revenue,
sum(if(month='Dec',revenue,null)) Dec_Revenue from Department group by id;

select id,
sum(case month when 'Jan' then revenue END) Jan_Revenue,
sum(case month when 'Feb' then revenue END) Feb_Revenue,
sum(case month when 'Mar' then revenue END) Mar_Revenue,
sum(if(month='Apr',revenue,null)) Apr_Revenue,
sum(if(month='May',revenue,null)) May_Revenue,
sum(if(month='Jun',revenue,null)) Jun_Revenue,
sum(if(month='Jul',revenue,null)) Jul_Revenue,
sum(if(month='Aug',revenue,null)) Aug_Revenue,
sum(if(month='Sep',revenue,null)) Sep_Revenue,
sum(if(month='Oct',revenue,null)) Oct_Revenue,
sum(if(month='Nov',revenue,null)) Nov_Revenue,
sum(if(month='Dec',revenue,null)) Dec_Revenue from Department group by id;