数据库高级查询【mysql】

发布于：2024-03-29 ⋅ 阅读:(22) ⋅ 点赞:(0)

数据库高级查询【数据库】

前言

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);




 */

最后

迎着日光月光星光，直面风霜雨霜雪霜。

本文含有隐藏内容，请 开通VIP 后查看

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布