几个复杂的mysql查询

--数据来源：附件table1，导入数据库
select * from Sheet1;

--1)查询各个年龄段的人数，结果输出年龄段及对应的人数，按年龄段升序排序

select 年龄段,count(id) as total from Sheet1
group by 年龄段
order by 年龄段 asc;

--2)查询每个工龄区间的人数，结果输出工龄区间及对应的人数，按人数降序排序   --提示: 分箱   case ...
/*三年以下（工龄<3）
--三年~七年（3<=工龄<7）
--七年~十年（7<=工龄<10）
--十年~十五年（10<=工龄<15）
十五年及以上（15<=工龄）
*/

select 工龄段, count(*) as 本段人数 from (  
    SELECT DISTINCT id,
    (CASE 
            WHEN 0<=工龄 and 工龄<3 THEN '三年以下'
            WHEN 3<=工龄 and 工龄<7 THEN '三年~七年'
            WHEN 7<=工龄 and 工龄<10 THEN '七年~十年'
            when 10<=工龄 and 工龄<15 then '十年~十五年'
            ELSE '十五年及以上'
    END) as 工龄段
    FROM Sheet1
) as a
group by 工龄段
order by count(*) desc;

--3)查询姓名重复三次的人员ID，结果以人员ID降序排序 

select id,a.人员姓名
from Sheet1
inner join 
(
    select 人员姓名
    from Sheet1
    group by 人员姓名
    having count(*)>=3) a
on Sheet1.人员姓名=a.人员姓名

--4)查询每个公司岗位类别为‘一般管理’的人员中，各个学历的占比，结果输出，公司、学历、占比

select a.公司简称,学历,num ,total as 本分公司人数,round( num / total * 100, 5 )  as 管理人员占比
    from 
    ( 
        select 公司简称,学历,count( id )as num 
        from Sheet1
        where 岗位类别='一般管理'
        group by 公司简称,学历
    ) a inner join
     ( 
               select 公司简称,count( id) as total from Sheet1 group by 公司简称
     ) b
    on a.公司简称=b.公司简称

--5)查询去掉最大年龄，最小年龄后人员的平均年龄（要求使用 NOT EXISTS）

SELECT avg(年龄)
FROM Sheet1
WHERE 年龄 not in (
(SELECT min(年龄) FROM Sheet1),
(SELECT max(年龄) FROM Sheet1));

select avg(a.年龄) 
from Sheet1 a
where not EXISTS
(
        select 1 from  
        ( select max(年龄) as age from Sheet1
           union all
          select min(年龄) as age from Sheet1
        )b    where a.年龄 = b.age
);

--数据来源：附件table2，导入数据库
select * from sheet2;

--1)查询新进类型中，不同原因的人数以及人数排名，结果输出 原因、人数、排名

 

select 变动原因,count(类型) as 人数,   rank() over( order by count(类型) desc) as 排名
from sheet2
where 类型='新进'
group by 变动原因;

select 变动原因,count(类型) as 人数,   row_number()  over( order by count(类型) desc) as 排名
from sheet2
where 类型='新进'
group by 变动原因;

select 变动原因,count(类型) as 人数,   dense_rank()  over( order by count(类型) desc) as 排名
from sheet2
where 类型='新进'
group by 变动原因;

--2)查询2009年度，每个月的公司变化人数（新进人数-离职人数），结果输出年度、月度、变化人数，结果根据年度、月度升序排序
--  行转列

SELECT 年度,月度,
    sum( CASE 类型 when '新进' then 1 else 0  end ) as '新进人数',
    sum( case 类型 when '离职' then 1 else 0  end) as '离职人数',
    sum( CASE 类型 when '新进' then 1 else 0  end ) -sum( case 类型 when '离职' then 1 else 0  end) as 变化人数
FROM sheet2 
GROUP BY  年度,月度 
order by 年度 desc,月度 desc

--3)查询累计到每个年度的离职人数,结果输出年度、累计离职人数（注意是累计不是合计）

select a.年度,a.离职人数, 
     ( select sum(离职人数) from 
                         (select 年度,count(年度) as 离职人数
                            from sheet2 
                            where 类型='离职'
                            group by 年度) b

     where b.年度 <= a.年度) as running_total
from 
    (select 年度,count(年度) as 离职人数
    from sheet2 
    where 类型='离职'
    group by 年度) a

--4)查询2009年度，每个月的离职人数以及环比增长率，结果根据年度、月度升序排序
--  知识: 环比增长率=（本期数-上期数）/上期数×100%。

--1. 当月离职数
select 年度,月度,count( 1 ) as 本月离职数
from sheet2
where 年度=2009 and 类型='离职'
GROUP BY 年度,月度
--2. 两表关联
select a.年度,a.月度, b.本月离职数-a.本月离职数, (b.本月离职数-a.本月离职数)/a.本月离职数 as 环比
from 
 (       select 年度,月度,count( 1 ) as 本月离职数
        from sheet2
        where 年度=2009 and 类型='离职'
        GROUP BY 年度,月度  ) a
inner join (
        select 年度,月度,count( 1 ) as 本月离职数
        from sheet2
        where 年度=2009 and 类型='离职'
        GROUP BY 年度,月度
)b
on a.年度=b.年度 and a.月度=b.月度-1