首页 > 试题广场 >

完成员工考核试卷突出的非领导员工

[编程题]完成员工考核试卷突出的非领导员工
  • 热度指数：21981 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
2021年结束，某公司组织了一场员工年终考核。作答用时少于同类试卷平均值且个人分数大于同类试卷总体平均值的员工记为该类型的突出员工。
有员工信息表 emp_info 如下：
（emp_id员工ID，emp_name员工姓名, emp_level员工等级, register_time入职时间，其中emp level＜7的是员工，其他是领导）
考核试卷信息表 examination_info 如下：
（exam_id试卷ID, tag试卷类别, duration考试时长, release_time发布时间）
试卷作答记录表 exam_record 如下：
（emp_id员工ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：
请你找到作答每类试卷的突出非领导员工，并输出他们的员工ID，员工等级和突出试卷类别并按照员工ID升序排序，若某员工两类试卷都突出，则按照试卷ID升序排序。
由示例数据结果输出如下：

解释：
9001试卷平均得分为78.667分，得分高于78.667且符合员工等级小于7的员工有1005、1007
9001试卷平均作答时间为27.3分钟，作答时间小于27.3分钟且符合员工等级小于7的员工有1006、1007
因此9001号卷突出员工为1007
9002试卷平均得分为75.833分，得分高于75.833且符合员工等级小于7的员工有1005、1006
9002试卷平均作答时间为34.2分钟，作答时间小于34.2分钟且符合员工等级小于7的员工有1006、1007
因此9002号卷突出员工为1006

示例1

输入

drop table if exists emp_info,examination_info,exam_record;
CREATE TABLE emp_info (
  emp_id INT NOT NULL,
  emp_name VARCHAR(45) NOT NULL,
  emp_level INT NOT NULL,
  register_time DATE NOT NULL,
  PRIMARY KEY (emp_id)
);

INSERT INTO emp_info VALUES (1001, '张老板', 10, '2017-10-01');
INSERT INTO emp_info VALUES (1002, '刘主管', 9, '2017-10-01');
INSERT INTO emp_info VALUES (1003, '李主任', 8, '2017-10-23');
INSERT INTO emp_info VALUES (1004, '王组长', 7, '2017-12-23');
INSERT INTO emp_info VALUES (1005, '张三', 4, '2020-01-22');
INSERT INTO emp_info VALUES (1006, '李四', 3, '2021-03-06');
INSERT INTO emp_info VALUES (1007, '王五', 3, '2021-07-04');

CREATE TABLE examination_info (
    exam_id int PRIMARY KEY ,
    tag varchar(32) NOT NULL ,
    duration int NOT NULL ,
    release_time datetime
);

INSERT INTO examination_info VALUES (9001, '企业文化', 60, '2021-12-29');
INSERT INTO examination_info VALUES (9002, '技术水平', 60, '2021-12-29');

CREATE TABLE exam_record (
    emp_id int NOT NULL,
    exam_id int NOT NULL,
    start_time datetime NOT NULL,
    submit_time datetime,
    score tinyint
);

INSERT INTO exam_record VALUES(1002, 9001, '2021-12-30 09:01:01', '2021-12-30 09:21:59', 80);
INSERT INTO exam_record VALUES(1002, 9002, '2021-12-31 09:01:01', '2021-12-31 09:41:42', 74);
INSERT INTO exam_record VALUES(1003, 9001, '2021-12-30 09:01:01', '2021-12-30 09:27:49', 90);
INSERT INTO exam_record VALUES(1003, 9002, '2021-12-31 09:01:01', '2021-12-31 09:37:24', 60);
INSERT INTO exam_record VALUES(1004, 9001, '2021-12-30 09:01:01', '2021-12-30 09:31:59', 60);
INSERT INTO exam_record VALUES(1004, 9002, '2021-12-31 09:01:01', '2021-12-31 09:26:07', 70);
INSERT INTO exam_record VALUES(1005, 9001, '2021-12-30 09:01:01', '2021-12-30 09:42:37', 83);
INSERT INTO exam_record VALUES(1005, 9002, '2021-12-31 09:01:01', '2021-12-31 09:51:43', 92);
INSERT INTO exam_record VALUES(1006, 9001, '2021-12-30 09:01:01', '2021-12-30 09:27:42', 66);
INSERT INTO exam_record VALUES(1006, 9002, '2021-12-31 09:01:01', '2021-12-31 09:32:06', 97);
INSERT INTO exam_record VALUES(1007, 9001, '2021-12-30 09:01:01', '2021-12-30 09:17:36', 93);
INSERT INTO exam_record VALUES(1007, 9002, '2021-12-31 09:01:01', '2021-12-31 09:21:59', 62);

输出

1006|3|技术水平
1007|3|企业文化
算法知识视频讲解
添加回答
Sqlite
牛客292332323号头像 牛客292332323号 
with aa as (select t2.tag
,t2.exam_id
,avg((unix_timestamp(t1.submit_time)-unix_timestamp(t1.start_time))) avg_time
,avg(t1.score) avg_score
from exam_record t1 
left join examination_info t2 
on t1.exam_id=t2.exam_id
group by t2.tag,t2.exam_id

)

select t1.emp_id
,t3.emp_level
,t2.tag exam_tag
from exam_record t1 
left join examination_info t2 
on t1.exam_id=t2.exam_id
left join emp_info t3 
on t1.emp_id=t3.emp_id
left join aa t4 
on t4.exam_id=t1.exam_id
where t1.score>t4.avg_score
and (unix_timestamp(t1.submit_time)-unix_timestamp(t1.start_time))<t4.avg_time
and t1.emp_id not in (
    select distinct emp_id
    from emp_info 
    where emp_level>=7
)
order by t1.emp_id,t1.exam_id

发表于 2026-01-28 13:53:35 回复(0)
Sqlite
MCMN头像 MCMN
SELECT
    emp_id,
    emp_level,
    exam_tag
FROM (
    SELECT
        a.emp_id,
        a.exam_id,
        TIMESTAMPDIFF(MINUTE, a.start_time, a.submit_time) as actual_time,
        a.score as actual_score,
        b.tag as exam_tag,
        c.emp_level,
        AVG(TIMESTAMPDIFF(MINUTE, a.start_time, a.submit_time)) OVER(PARTITION BY a.exam_id) as avg_time,
        AVG(a.score) OVER(PARTITION BY a.exam_id) as avg_score
    FROM exam_record a
    JOIN examination_info b ON a.exam_id = b.exam_id
    JOIN emp_info c ON a.emp_id = c.emp_id
) t1
WHERE emp_level < 7
    AND actual_time < avg_time
    AND actual_score > avg_score
ORDER BY emp_id, exam_id;
发表于 2025-11-27 15:14:17 回复(0)
Sqlite
赶紧自律起来头像 赶紧自律起来 
select
    e.emp_id,
    e.emp_level,
    o.tag
from
    emp_info e
    left join exam_record d on e.emp_id = d.emp_id
    left join examination_info o on o.exam_id = d.exam_id
    left join (
        select
            exam_id,
            avg(timestampdiff(second, start_time, submit_time)) as avgexam_time,
            avg(score) as avg_score
        from
            exam_record
        group by
            exam_id
    ) as p on p.exam_id = d.exam_id
where
    e.emp_level < 7
    and timestampdiff(second, d.start_time, d.submit_time) < p.avgexam_time
    and d.score > p.avg_score
order by
    e.emp_id asc,
    d.exam_id asc

发表于 2025-09-06 16:20:55 回复(0)
Sqlite
东方宸星头像 东方宸星 
WITH avg_info AS (
    SELECT
        exam_id,
        AVG(score) AS avg_score,
        AVG(TIMESTAMPDIFF(SECOND, start_time, submit_time)) AS avg_time
    FROM 
        exam_record
    GROUP BY exam_id
)
SELECT
    emp_id,
    emp_level,
    tag AS exam_tag
FROM 
    exam_record
    LEFT JOIN avg_info USING (exam_id)
    LEFT JOIN emp_info USING (emp_id)
    LEFT JOIN examination_info USING (exam_id)
WHERE 
    emp_level < 7
    AND score > avg_score
    AND TIMESTAMPDIFF(SECOND, start_time, submit_time) < avg_time
ORDER BY 
    emp_id,
    exam_id

发表于 2025-06-30 22:26:02 回复(0)
Sqlite
我真的也叫乐杨头像 我真的也叫乐杨
#计算平均时间和平均成绩
with t1 as
(select exam_id, avg(timestampdiff(second,start_time,submit_time)/60) as avg_time, avg(score) as avg_score from exam_record
group by exam_id),
#找出考试时间小于平均时间且考试分数大于平均分数的人
t2 as
(select emp_id, er.exam_id, score from exam_record er
left join t1 on er.exam_id = t1.exam_id
where timestampdiff(second,start_time,submit_time)/60 < t1.avg_time
and score > t1.avg_score)
#剔除领导
select t2.emp_id as emp_id, emp_level, tag from t2 left join emp_info emi on t2.emp_id = emi.emp_id
left join examination_info exi on t2.exam_id = exi.exam_id
where emp_level < 7
order by emp_id, t2.exam_id
发表于 2025-04-20 01:19:43 回复(0)
Sqlite
牛客611160964号头像 牛客611160964号 
WITH
    emp_lower_level AS (
        SELECT
            emp_id,
            emp_level
        FROM
            emp_info
        WHERE
            emp_level < 7
    ),
    exam_details AS (
        SELECT
            r.emp_id,
            e.emp_level,
            r.exam_id,
            TIMESTAMPDIFF (MINUTE, r.start_time, r.submit_time) AS duration,
            r.score,
            i.tag
        FROM
            emp_lower_level e
            INNER JOIN exam_record r ON e.emp_id = r.emp_id
            INNER JOIN examination_info i ON r.exam_id = i.exam_id
    ),
    exam_averages AS (
        SELECT
            exam_id,
            AVG(duration) AS avg_duration,
            AVG(score) AS avg_score
        FROM
            exam_details
        GROUP BY
            exam_id
    )
SELECT
    ed.emp_id,
    ed.emp_level,
    ed.tag
FROM
    exam_details ed
    INNER JOIN exam_averages ea ON ed.exam_id = ea.exam_id
WHERE
    ed.duration < ea.avg_duration
    AND ed.score > ea.avg_score
GROUP BY
    ed.emp_id,
    ed.emp_level,
    ed.exam_id,
    ed.tag
ORDER BY
    ed.emp_id,
    ed.emp_level,
    ed.tag,
    ed.exam_id;

发表于 2025-03-25 11:58:22 回复(0)
提交观点

问题信息

SQL
难度：
7条回答 512浏览

热门推荐

相关试题

完成员工考核试卷突出的非领导员工

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号