获取昨天的日期 日期相减函数 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 ;