--数据来源:附件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