按照月份统计入职人数
select
date_format( ss_staff_job.begindate, '%m' ) month,
count(*)
from
ss_staff_job
where
date_format( ss_staff_job.begindate, '%Y' )= 2017
group by
date_format( ss_staff_job.begindate, '%m' )
按照年龄区间统计人数
explain select
sum( case when age >= 0 and age <= 25 then 1 else 0 end ) as '25以下',
sum( case when age >= 26 and age <= 30 then 1 else 0 end ) as '26-30',
sum( case when age >= 31 and age <= 45 then 1 else 0 end ) as '31-45',
sum( case when age >= 46 and age <= 55 then 1 else 0 end ) as '46-55',
sum( case when age >= 56 then 1 else 0 end ) as '56以上'
from
(
select
date_format( from_days( to_days( now())- to_days( birthdate )), '%Y' )+ 0 as age
from
ss_staff inner join ss_staff_orgrel on
ss_staff.id = ss_staff_orgrel.staff_id inner join ss_staff_job on
ss_staff_orgrel.id = ss_staff_job.orgrel_id
where
ss_staff.birthdate is not null
) tmp
date_format函数
select date_format('2018-01-31','%Y-%m-%d-%M-%y-%D') from dual
结果：2018-01-31-January-18-31st
根据出生日期计算年龄:
select date_format( from_days( to_days( now())- to_days( birthdate )), '%Y' )+ 0 as age from dual
from_days(to_days(now())-to_days('2014-01-21'))得到的是两个日期相差的日期 0004-02-05
查看索引
show index from ss_staff_edu
为表添加索引
create index idx_cate_browse_buy on course(category_id,browse_times,buy_times);
下面是操作索引的一些语法：
http://www.jb51.net/article/73372.htm
对null排序
image.png
现在null在前面
select * from testnull order by coalesce(id,999999999);
