-- 从招聘表、公司表两站表中查处每年每月招聘人数为前十名的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