几个复杂的mysql查询

Love The Way You Lie 2022-08-29 12:51 269阅读 0赞

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neWluZ2NoZW5ncWk_size_16_color_FFFFFF_t_70][]\--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;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neWluZ2NoZW5ncWk_size_16_color_FFFFFF_t_70 1][]  
\--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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neWluZ2NoZW5ncWk_size_16_color_FFFFFF_t_70]: /images/20220829/3ce2ba3701bb4ac8a953e65f677600e8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neWluZ2NoZW5ncWk_size_16_color_FFFFFF_t_70 1]: /images/20220829/f807d9a3408c45f3b3da3c7c04d99a6a.png