mysql 分组聚合事例

--  从招聘表、公司表两站表中查处每年每月招聘人数为前十名的sql

（1）执行如下sql获得如图所示，

select
year(r.create_time) as school_year,
month(r.create_time) as school_month,
c.full_name as companyName,
sum(r.recruit_cnt) as number,@rownum:=0 as rownum
from recruit r,company c
where r.company_id = c.company_id
group by school_year,school_month,companyName
order by school_year,school_month,number desc

图一  将每个月份招聘人数从多到少排列

（2）以年月分组（设置变量的方法）列转行的方法  得到每个月份的前十名（如图）

SELECT 

school_year,school_month,

MAX(CASE rownum WHEN 1 THEN companyName ELSE '' END) as num_one, -- 列转行统计数据
MAX(CASE rownum WHEN 2 THEN companyName ELSE '' END) as num_two,
MAX(CASE rownum WHEN 3 THEN companyName ELSE '' END) as num_three,
MAX(CASE rownum WHEN 4 THEN companyName ELSE '' END) as num_four,
MAX(CASE rownum WHEN 5 THEN companyName ELSE '' END) as num_five,
MAX(CASE rownum WHEN 6 THEN companyName ELSE '' END) as num_six,
MAX(CASE rownum WHEN 7 THEN companyName ELSE '' END) as num_seven,
MAX(CASE rownum WHEN 8 THEN companyName ELSE '' END) as num_eight,
MAX(CASE rownum WHEN 9 THEN companyName ELSE '' END) as num_nine,
MAX(CASE rownum WHEN 10 THEN companyName ELSE '' END) as num_ten,
NOW() AS create_time
FROM(
SELECT school_year,school_month,companyName,number,
  if(@year=school_year and @month=school_month,@rownum:=@rownum+1,@rownum:=1) as rownum,    -- 
@year:=school_year,@month:=school_month 

FROM (
select
year(r.create_time) as school_year,
month(r.create_time) as school_month,
c.full_name as companyName,
sum(r.recruit_cnt) as number,@rownum:=0 as rownum
from recruit r,company c
where r.company_id = c.company_id
group by school_year,school_month,companyName
order by school_year,school_month,number desc

) a

) b
GROUP BY school_year,school_month