数据库高级查询【数据库】
- 前言
- 版权
- 推荐
- 数据库高级查询
- 行转列
- 统计数据 CASE WHEN 条件 THEN 结果1 ELSE 结果2 END
- 数据库查询带排名
- 建表
- 查询带排名，排名连续
- 查询带排名，排名不连续
- 查询带排名，按行号
- Mysql数据库函数
- 常用函数
- 最后
前言
2024-3-24 20:58:38
以下内容源自《【数据库】》
仅供学习交流使用
版权
禁止其他平台发布时删除以下此话
本文首次发布于CSDN平台
作者是CSDN@日星月云
博客主页是https://jsss-1.blog.csdn.net
禁止其他平台发布时删除以上此话
推荐
MySQL面试50题【mysql】
数据库高级查询
行转列
有一张学生成绩表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);
*/
统计数据 CASE WHEN 条件 THEN 结果1 ELSE 结果2 END
与Mysql面试50题中18题一样
现有某公司人事相关表：
员工信息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);
*/
结果
数据库查询带排名
数据库查询-分数排名
建表
create table scores
(
id int null,
score int null
);
INSERT INTO bs_sql.scores (id, score) VALUES (1, 98);
INSERT INTO bs_sql.scores (id, score) VALUES (2, 100);
INSERT INTO bs_sql.scores (id, score) VALUES (3, 97);
INSERT INTO bs_sql.scores (id, score) VALUES (4, 98);
INSERT INTO bs_sql.scores (id, score) VALUES (5, 99);
INSERT INTO bs_sql.scores (id, score) VALUES (6, 99);
查询带排名，排名连续
按成绩降序，学号升序
成绩相同排名相同，排名连续
SELECT id,score,DENSE_RANK() OVER (ORDER BY score DESC ) AS 'rk'
FROM scores
ORDER BY score DESC ,id ASC ;
查询带排名，排名不连续
按成绩降序，学号升序
成绩相同排名相同，排名不连续
SELECT id,score,RANK() OVER (ORDER BY score DESC ) AS 'rk'
FROM scores
ORDER BY score DESC ,id ASC ;
查询带排名，按行号
与Mysql面试50题中19题一样
按成绩降序，学号升序，按行号
SELECT id,score,ROW_NUMBER() OVER (ORDER BY score DESC ) AS 'rk'
FROM scores
ORDER BY score DESC ,id ASC ;
Mysql数据库函数
Mysql数据库函数【Mysql】
常用函数
| 函数 | 解释 |
|---|---|
| ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近x的值，并保留到小数点后面Y位 |
| CONCAT(s1,s2…n) | 连接s1,s2…,sn为一个字符串 |
| YEAR(date)MONTH(date) / DAY(date) | 返回具体的日期值 |
| HOUR(time) MINUTE(time) /SECOND(time) | 返回具体的时间值、 |
| DATEDIFF(date1,date2) | 返回date1 - date2的日期间隔天数 |
| CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2…[ELSE resultn] END | 相当于Java的if…else if…else… |
现有课程信息表course_info_tb(cid 课程ID,tag 视频类别 release_date 发布日期,duration 视频时长）
示例数据如下
用户观看记录表play_record_tb(uid 用户ID,cid 课程ID,start time 开始观看时间,end_time 结束观看时间,score 用户评分）
请统计每类视频的平均播放进度，对于每条播放记录，播放进度=播放时长÷视频时长。特殊地，如果播放时长大于视频时长，则播放进度记为100%。结果以百分数的形式保留一位小数。结果按播放进度降序 排序示例输出如下
tag avg_play_progress
c++ 61.1%
python 34.4%
/*
现有课程信息表course_info_tb(cid 课程ID,tag 视频类别 release_date 发布日期,duration 视频时长）
示例数据如下
用户观看记录表play_record_tb(uid 用户ID,cid 课程ID,start time 开始观看时间,end_time 结束观看时间,score 用户评分）
请统计每类视频的平均播放进度，对于每条播放记录，播放进度=播放时长÷视频时长。特殊地，如果播放时长大于视频时长，则播放进度记为100%。结果以百分数的形式保留一位小数。结果按播放进度降序 排序示例输出如下
tag avg_play_progress
c++ 61.1%
python 34.4%
*/
SELECT
c.tag,
CONCAT(
ROUND(AVG(
CASE
WHEN end_time < start_time THEN 0
WHEN end_time > DATE_ADD(start_time, INTERVAL c.duration MINUTE) THEN 100
ELSE (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) / (c.duration * 60) * 100
END
), 1)
,'%') AS avg_play_progress
FROM play_record_tb p
JOIN course_info_tb c ON p.cid = c.cid
GROUP BY c.tag
ORDER BY avg_play_progress DESC;
/*
create table course_info_tb
(
id int null,
cid int null,
release_date date null,
duration int null,
tag varchar(16) null
);
INSERT INTO ms_blibli.course_info_tb (id, cid, release_date, duration, tag) VALUES (1, 9001, '2022-01-01', 60, 'c++');
INSERT INTO ms_blibli.course_info_tb (id, cid, release_date, duration, tag) VALUES (2, 9002, '2022-01-01', 90, 'python');
INSERT INTO ms_blibli.course_info_tb (id, cid, release_date, duration, tag) VALUES (3, 9003, '2022-01-01', 45, 'sql');
INSERT INTO ms_blibli.course_info_tb (id, cid, release_date, duration, tag) VALUES (4, 9004, '2022-01-02', 45, 'java');
create table play_record_tb
(
id int null,
uid int null,
cid int null,
start_time datetime null,
end_time datetime null,
score int null
);
INSERT INTO ms_blibli.play_record_tb (id, uid, cid, start_time, end_time, score) VALUES (1, 1001, 9001, '2022-01-01 08:30:00', '2022-01-01 09:00:00', 5);
INSERT INTO ms_blibli.play_record_tb (id, uid, cid, start_time, end_time, score) VALUES (2, 1001, 9002, '2022-01-02 08:30:00', '2022-01-02 09:01:00', 4);
INSERT INTO ms_blibli.play_record_tb (id, uid, cid, start_time, end_time, score) VALUES (3, 1001, 9001, '2022-01-03 09:30:00', '2022-01-03 10:20:00', 5);
INSERT INTO ms_blibli.play_record_tb (id, uid, cid, start_time, end_time, score) VALUES (4, 1002, 9001, '2022-01-01 08:30:00', '2022-01-01 09:00:00', 3);
*/
最后
迎着日光月光星光，直面风霜雨霜雪霜。
