优百信息 2024/03/21【笔试真题】
前言
2024-3-24 23:09:49
私密发布于
2024-03-24 23:26:22
公开发布于
2024-5-23 11:16:09
以下内容源自《【笔试真题】》
仅供学习交流使用
版权
禁止其他平台发布时删除以下此话
本文首次发布于CSDN平台
作者是CSDN@日星月云
博客主页是https://jsss-1.blog.csdn.net
禁止其他平台发布时删除以上此话
推荐
无
优百信息 2024/04/21
3 行转列
3.有一张学生成绩表sc(name姓名，class 课程，score 成绩)，示例如下:
请使用 sql输出如下信息
# 3
/*
3.有一张学生成绩表sc(name姓名，class 课程，score 成绩)，示例如下:
请使用 sql输出如下信息
*/
SELECT T.Name,
MAX(T.语文) '语文',
MAX(T.数学) '数学',
MAX(T.英语) '英语'
FROM (SELECT Name,
CASE WHEN class = '语文' THEN score ELSE 0 END AS '语文',
CASE WHEN class = '数学' THEN score ELSE 0 END AS '数学',
CASE WHEN class = '英语' THEN score ELSE 0 END AS '英语'
FROM sc_2403) T
GROUP BY T.Name;
/*
create table sc_2403
(
name varchar(16) null,
class varchar(16) null,
score int null
);
INSERT INTO sc_2403 (name, class, score) VALUES ('小花', '语文', 98);
INSERT INTO sc_2403 (name, class, score) VALUES ('小花', '英语', 95);
INSERT INTO sc_2403 (name, class, score) VALUES ('小花', '数学', 100);
INSERT INTO sc_2403 (name, class, score) VALUES ('小明', '语文', 93);
INSERT INTO sc_2403 (name, class, score) VALUES ('小明', '英语', 89);
INSERT INTO sc_2403 (name, class, score) VALUES ('小明', '数学', 96);
*/
4 GROUP BY
4.现有某公司人事相关表：
员工信息employee(编号code，姓名ame，性别sex，所属部门department)
OA流程审核信息process_info(流程编码id，流程名称name，审核时间(Date类型)approve_dale,审核人员工编码approve_code,申请人员工编码 apply_code,审核结果approve_result(Y通过，审核时长duration(毫秒))
请用SQL语统计审核时为“2024年3月”流程名称为“请假”的每个审核人员【员工姓名】、【审核总量】、【审核通过数量】、【驳回数量】【平均审核时间(小时)】、【最大审核时间(小时)】、【总审核时间(小时)】
employee表数据实例如下：
process_info表数据实例如下：
# 4
/*
4.现有某公司人事相关表：
员工信息employee(编号code，姓名ame，性别sex，所属部门department)
OA流程审核信息process_info(流程编码id，流程名称name，审核时间(Date类型)approve_dale,审核人员工编码approve_code,申请人员工编码 apply_code,审核结果approve_result(Y通过，审核时长duration(毫秒))
请用SQL语统计审核时为“2024年3月”流程名称为“请假”的每个审核人员【员工姓名】、【审核总量】、【审核通过数量】、【驳回数量】【平均审核时间(小时)】、【最大审核时间(小时)】、【总审核时间(小时)】
employee表数据实例如下：
process_info表数据实例如下：
*/
SELECT e.name AS '员工姓名',
count AS '审核总量',
sumY AS '审核通过数量',
sumN AS '驳回数量',
avg AS '平均审核时间(小时)',
max AS '最大审核时间(小时)',
sum AS '总审核时间(小时)'
FROM employee e
JOIN (SELECT approve_code,
COUNT(approve_result) AS count,
SUM(CASE WHEN approve_result = 'Y' THEN 1 ELSE 0 END) AS sumY,
SUM(CASE WHEN approve_result = 'N' THEN 1 ELSE 0 END) AS sumN,
AVG(duration) / 3600000 AS avg,
MAX(duration) / 3600000 AS max,
SUM(duration) / 3600000 AS sum
FROM process_info
WHERE DATE_FORMAT(approve_date, '%Y-%m') = '2024-03'
AND process_info.name = '请假'
GROUP BY approve_code
) p
ON e.code = p.approve_code;
/*
create table employee
(
code varchar(16) null,
name varchar(16) null,
sex varchar(16) null,
department varchar(16) null
);
INSERT INTO ms_test.employee (code, name, sex, department) VALUES ('14140294', '小花', '女', '人力资源部');
INSERT INTO ms_test.employee (code, name, sex, department) VALUES ('02050240', '小明', '男', '总经办');
INSERT INTO ms_test.employee (code, name, sex, department) VALUES ('02050241', '小小', '女', '研发部');
create table process_info
(
id int null,
approve_code varchar(16) null,
apply_code varchar(16) null,
name varchar(16) null,
approve_result varchar(16) null,
approve_date datetime null,
duration int null
);
INSERT INTO ms_test.process_info (id, approve_code, apply_code, name, approve_result, approve_date, duration) VALUES (1, '14140294', '02050254', '出差', 'N', '2024-03-22 11:17:35', 528000);
INSERT INTO ms_test.process_info (id, approve_code, apply_code, name, approve_result, approve_date, duration) VALUES (2, '14140294', '02050255', '出差', 'N', '2024-03-22 11:17:37', 827000);
INSERT INTO ms_test.process_info (id, approve_code, apply_code, name, approve_result, approve_date, duration) VALUES (3, '14140294', '02050256', '出差', 'N', '2024-03-22 11:17:38', 942000);
INSERT INTO ms_test.process_info (id, approve_code, apply_code, name, approve_result, approve_date, duration) VALUES (4, '02050240', '02050257', '请假', 'Y', '2024-03-22 11:17:39', 3242000);
INSERT INTO ms_test.process_info (id, approve_code, apply_code, name, approve_result, approve_date, duration) VALUES (5, '02050241', '02050258', '请假', 'Y', '2024-03-22 11:17:41', 4116000);
INSERT INTO ms_test.process_info (id, approve_code, apply_code, name, approve_result, approve_date, duration) VALUES (6, '02050240', '02050259', '请假', 'Y', '2024-03-22 11:17:42', 738000);
*/
结果
5 JOIN ON
5.有如下2张表:
学生表student:
成绩表score:
请使用sql输出所有成绩和所有学生结果示例结果如下:
# 5.
/*
5.有如下2张表:
学生表student:
成绩表score:
请使用sql输出所有成绩和所有学生结果示例结果如下:
*/
SELECT st.name, sc.score, sc.class
FROM student st
LEFT JOIN score sc ON st.no = sc.sno
UNION
SELECT st.name, sc.score, sc.class
FROM student st
RIGHT JOIN score sc ON st.no = sc.sno;
/*
create table student
(
no int null,
name varchar(16) null,
sex varchar(16) null
);
INSERT INTO ms_test.student (no, name, sex) VALUES (1, '小一', '女');
INSERT INTO ms_test.student (no, name, sex) VALUES (2, '小二', '男');
INSERT INTO ms_test.student (no, name, sex) VALUES (3, '小三', '男');
INSERT INTO ms_test.student (no, name, sex) VALUES (4, '小四', '女');
create table score
(
sno int null,
class varchar(16) null,
score int null
);
INSERT INTO ms_test.score (sno, class, score) VALUES (1, '语文', 98);
INSERT INTO ms_test.score (sno, class, score) VALUES (1, '英语', 95);
INSERT INTO ms_test.score (sno, class, score) VALUES (1, '数学', 100);
INSERT INTO ms_test.score (sno, class, score) VALUES (2, '语文', 93);
INSERT INTO ms_test.score (sno, class, score) VALUES (2, '英语', 98);
INSERT INTO ms_test.score (sno, class, score) VALUES (2, '数学', 96);
INSERT INTO ms_test.score (sno, class, score) VALUES (5, '英语', 70);
INSERT INTO ms_test.score (sno, class, score) VALUES (4, '语文', 89);
*/
最后
2024-3-24 23:19:50
迎着日光月光星光，直面风霜雨霜雪霜。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
