- 获取昨天的日期
日期相减函数 datediff
select datediff(curdate(),INTERVAL 1 DAY) ;
- 删除Person表中重复的Email数据,只保留id最小的一条
delete p1 from Person p1,Person p2 where p1.Email = p2.Email and p1.Id > p2.Id;
- 换座位,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;
- 交换性别,‘f’ 变 ‘m’, ‘m’ 变 ‘f’
update salary set sex=
case sex when 'f' then 'm'
else 'f'
END;
-
月份列变行,求总和,并汇总
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;