mysql疯狂练习——02

最新推荐文章于 2024-06-05 17:05:41 发布

有智青年i

最新推荐文章于 2024-06-05 17:05:41 发布

阅读量206

点赞数 9

分类专栏： MySQL 文章标签： mysql android 数据库

本文链接：https://blog.csdn.net/m0_64622390/article/details/134170220

版权

MySQL 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

1.请输出每份试卷每月作答数和截止当月的作答总数。

drop table if exists exam_record;
CREATE TABLE exam_record
(
    id          int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid         int      NOT NULL COMMENT '用户ID',
    exam_id     int      NOT NULL COMMENT '试卷ID',
    start_time  datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score       tinyint COMMENT '得分'
) CHARACTER SET utf8
  COLLATE utf8_general_ci;

INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score)
VALUES (1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 90),
       (1002, 9001, '2020-01-20 10:01:01', '2020-01-20 10:10:01', 89),
       (1002, 9001, '2020-02-01 12:11:01', '2020-02-01 12:31:01', 83),
       (1003, 9001, '2020-03-01 19:01:01', '2020-03-01 19:30:01', 75),
       (1004, 9001, '2020-03-01 12:01:01', '2020-03-01 12:11:01', 60),
       (1003, 9001, '2020-03-01 12:01:01', '2020-03-01 12:41:01', 90),
       (1002, 9001, '2020-05-02 19:01:01', '2020-05-02 19:32:00', 90),
       (1001, 9002, '2020-01-02 19:01:01', '2020-01-02 19:59:01', 69),
       (1004, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99),
       (1003, 9002, '2020-02-02 12:01:01', '2020-02-02 12:31:01', 68),
       (1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81),
       (1001, 9002, '2020-03-02 12:11:01', null, null);

# 请输出每份试卷每月作答数和截止当月的作答总数。
select exam_id, month(submit_time), count(1)
from exam_record
where score is not null
group by exam_id, month(submit_time);

SELECT *, SUM(month_cnt) OVER (PARTITION BY exam_id ORDER BY start_month) cum_exam_cnt
FROM (
         SELECT exam_id, DATE_FORMAT(start_time, '%Y%m') start_month, COUNT(start_time) month_cnt
         FROM exam_record
         GROUP BY exam_id, start_month
     ) t1

2.查询并返回 Employee 表中第二高的薪水。如果不存在第二高的薪水，查询应该返回 null(Pandas 则返回 None) 。

Create table If Not Exists Employee (id int, salary int);
Truncate table Employee;
insert into Employee (id, salary) values ('1', '100');
insert into Employee (id, salary) values ('2', '200');
insert into Employee (id, salary) values ('3', '300');

# 查询并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null(Pandas 则返回 None) 。
SELECT DISTINCT
    Salary AS SecondHighestSalary
FROM
    Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1

3. 查询并对分数进行排序。排名按以下规则计算:
分数应按从高到低排列。
如果两个分数相等，那么两个分数的排名应该相同。
在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。
按 score 降序返回结果表。

Create table If Not Exists Scores (id int, score DECIMAL(3,2));
Truncate table Scores;
insert into Scores (id, score) values ('1', '3.5');
insert into Scores (id, score) values ('2', '3.65');
insert into Scores (id, score) values ('3', '4.0');
insert into Scores (id, score) values ('4', '3.85');
insert into Scores (id, score) values ('5', '4.0');
insert into Scores (id, score) values ('6', '3.65');

/*
 查询并对分数进行排序。排名按以下规则计算:
分数应按从高到低排列。
如果两个分数相等，那么两个分数的排名应该相同。
在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。
按 score 降序返回结果表。
 */
 select id,dense_rank() over (order by score desc ) as `rank`
from Scores;

SELECT
  S.score,
  DENSE_RANK() OVER (
    ORDER BY
      S.score DESC
  ) AS 'rank'
FROM
  Scores S;

4.请你统计11月份日人均回答量（回答问题数量/答题人数），按回答日期排序，结果保留两位小数

drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL,
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E001' ,150);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb VALUES('2021-11-1',102, 'C003' ,50);
INSERT INTO answer_tb VALUES('2021-11-1' ,103, 'P001', 35);
INSERT INTO answer_tb VALUES('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb VALUES('2021-11-1' ,105, 'P001', 125);
INSERT INTO answer_tb VALUES('2021-11-1' , 102, 'P002', 105);
INSERT INTO answer_tb VALUES('2021-11-2',  101, 'P001' ,201);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C002', 200);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C001', 225);
INSERT INTO answer_tb VALUES('2021-11-2' , 110, 'C002', 220);
INSERT INTO answer_tb VALUES('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb VALUES('2021-11-4' ,109, 'E003', 130);
INSERT INTO answer_tb VALUES('2021-11-4', 109, 'E001',123);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C001',160);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb VALUES('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb VALUES('2021-11-5' , 106, 'P002' , 45);
INSERT INTO answer_tb VALUES('2021-11-5' , 107, 'E003', 56);

-- 请你统计11月份日人均回答量（回答问题数量/答题人数），按回答日期排序，结果保留两位小数
select answer_date,round(count(issue_id)/count(distinct author_id),2) as per_num
from answer_tb
group by answer_date;

5.回答字数大于等于100字的认为是高质量回答，请你统计某乎问答高质量的回答中用户属于1-2级、3-4级、5-6级的数量分别是多少，按数量降序排列

drop table if exists author_tb;
CREATE TABLE author_tb
(
    author_id    int(10)  NOT NULL,
    author_level int(10)  NOT NULL,
    sex          char(10) NOT NULL
);
INSERT INTO author_tb
VALUES (101, 6, 'm');
INSERT INTO author_tb
VALUES (102, 1, 'f');
INSERT INTO author_tb
VALUES (103, 1, 'm');
INSERT INTO author_tb
VALUES (104, 3, 'm');
INSERT INTO author_tb
VALUES (105, 4, 'f');
INSERT INTO author_tb
VALUES (106, 2, 'f');
INSERT INTO author_tb
VALUES (107, 2, 'm');
INSERT INTO author_tb
VALUES (108, 5, 'f');
INSERT INTO author_tb
VALUES (109, 6, 'f');
INSERT INTO author_tb
VALUES (110, 5, 'm');

drop table if exists answer_tb;
CREATE TABLE answer_tb
(
    answer_date date     NOT NULL,
    author_id   int(10)  NOT NULL,
    issue_id    char(10) NOT NULL,
    char_len    int(10)  NOT NULL
);
INSERT INTO answer_tb
VALUES ('2021-11-1', 101, 'E001', 150);
INSERT INTO answer_tb
VALUES ('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb
VALUES ('2021-11-1', 102, 'C003', 50);
INSERT INTO answer_tb
VALUES ('2021-11-1', 103, 'P001', 35);
INSERT INTO answer_tb
VALUES ('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb
VALUES ('2021-11-1', 105, 'P001', 125);
INSERT INTO answer_tb
VALUES ('2021-11-1', 102, 'P002', 105);
INSERT INTO answer_tb
VALUES ('2021-11-2', 101, 'P001', 201);
INSERT INTO answer_tb
VALUES ('2021-11-2', 110, 'C002', 200);
INSERT INTO answer_tb
VALUES ('2021-11-2', 110, 'C001', 225);
INSERT INTO answer_tb
VALUES ('2021-11-2', 110, 'C002', 220);
INSERT INTO answer_tb
VALUES ('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb
VALUES ('2021-11-4', 109, 'E003', 130);
INSERT INTO answer_tb
VALUES ('2021-11-4', 109, 'E001', 123);
INSERT INTO answer_tb
VALUES ('2021-11-5', 108, 'C001', 160);
INSERT INTO answer_tb
VALUES ('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb
VALUES ('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb
VALUES ('2021-11-5', 106, 'P002', 45);
INSERT INTO answer_tb
VALUES ('2021-11-5', 107, 'E003', 56);

-- 回答字数大于等于100字的认为是高质量回答，请你统计某乎问答高质量的回答中用户属于1-2级、3-4级、5-6级的数量分别是多少，按数量降序排列
select case
           when author_level in (1,2) then '1-2级'
           when author_level in (3,4) then '3-4级'
           when author_level in (5,6) then '5-6级'
           else '' end as level_cut,
       count(issue_id)    num

from answer_tb a
         join author_tb t on a.author_id = t.author_id
where a.char_len >= 100
group by level_cut
order by num desc
;