分析员工在不同项目中的绩效表现以及所属部门的平均绩效情况_

【背景】：一家公司需要分析员工在不同项目中的绩效表现以及所属部门的平均绩效情况。
【原始表】：
employee_projects（员工项目）表:

employee_id (员工 ID): 员工的唯一标识符
project_id (项目 ID): 员工参与的项目 ID
performance_score (绩效得分): 员工在该项目中的绩效得分

department_info（部门信息）表:

employee_id (员工 ID): 员工的唯一标识符
department_id (部门 ID): 部门的唯一标识符
department_name (部门名称): 部门的名称

【要求】：根据上面这两个表格，查询每个部门中绩效得分高于该部门平均绩效得分的员工信息。查询出来的数据按照员工 ID 升序排列。要求查询出来的表格的字段如下：

employee_id: 员工 ID。
department_name: 部门名称。
performance_score: 绩效得分。

【示例】：

employee_projects（员工项目）表:

department_info（部门信息）表:

【按要求查询出来的表】

【解释】

上述表中，员工共有5个，2个研发部，3个市场部的，市场部的绩效平均分是85，研发部的绩效平均分是80，超过市场部平均分的分别是员工ID 2和5，超过研发部绩效平均分的是员工ID 1

输入

DROP TABLE IF EXISTS employee_projects;
DROP TABLE IF EXISTS department_info;
-- 创建表
CREATE TABLE employee_projects (
    employee_id INT PRIMARY KEY,
    project_id INT,
    performance_score INT
);

CREATE TABLE department_info (
    employee_id INT PRIMARY KEY,
    department_id INT ,
    department_name VARCHAR(50)
);

-- 插入数据
INSERT INTO employee_projects (employee_id, project_id, performance_score)
VALUES (1, 1, 85),
       (2, 2, 90),
       (3, 1, 75),
       (4, 2, 75),
       (5, 2, 90);

INSERT INTO department_info (employee_id, department_id,department_name)
VALUES (1, 1, '研发部'),
       (2, 2, '市场部'),
       (3, 1, '研发部'),
       (4, 2, '市场部'),
       (5, 2, '市场部');

select * from employee_projects;
select * from department_info;

select employee_id, t1.department_name, performance_score from ( select department_name, avg(performance_score) avg_score from employee_projects a join department_info b on a.employee_id = b.employee_id group by department_name ) t1 join ( select a.employee_id, department_name, performance_score from employee_projects a join department_info b on a.employee_id = b.employee_id ) t2 on t1.department_name = t2.department_name where performance_score > avg_score order by employee_id

select employee_id, department_name, performance_score from ( select e.employee_id, d.department_name, e.performance_score, avg(e.performance_score) over(partition by d.department_name) as avg_score from employee_projects as e left join department_info as d on e.employee_id=d.employee_id) as t where performance_score> avg_score order by employee_id

分析员工在不同项目中的绩效表现以及所属部门的平均绩效情况

输入

输出

问题信息

热门推荐

相关试题