SQL 进阶挑战篇

withoutfear

已于 2022-03-01 10:00:21 修改

阅读量1.6k

点赞数 2

分类专栏： MySQL 文章标签： sql 数据库 database

于 2022-02-26 17:31:11 首次发布

本文链接：https://blog.csdn.net/withoutfear/article/details/123151891

版权

MySQL 专栏收录该内容

10 篇文章 2 订阅

订阅专栏

01 增删改操作

插入记录

SQL1 插入记录（一）

现在有两个用户的作答记录详情如下：

用户1001在2021年9月1日晚上10点11分12秒开始作答试卷9001，并在50分钟后提交，得了90分；
用户1002在2021年9月4日上午7点1分2秒开始作答试卷9002，并在10分钟后退出了平台。

请用一条语句将这两条记录插入表中。

INSERT INTO exam_record VALUES
(null, 1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(null, 1002, 9002, '2021-09-04 07:01:02', null, null);

SQL2 插入记录（二）

将2021年之前的已完成了的试题作答纪录导入到exam_record_before_2021表来备份2021年之前的试题作答记录。

INSERT INTO exam_record_before_2021
SELECT NULL, uid, exam_id, start_time, submit_time, score
FROM exam_record
WHERE submit_time < '2021-01-01 00:00:00';

SQL3 插入记录（三）

将 2021-01-01 00:00:00 作为发布时间插入到试题信息表examination_info，不管该ID试卷是否存在，都要插入成功。

REPLACE INTO examination_info
(id, exam_id, tag, difficulty, duration, release_time)
VALUES(null, 9003, 'SQL', 'hard', 90, '2021-01-01 00:00:00')

更新记录

SQL4 更新记录（一）

把examination_info表中tag为PYTHON的tag字段全部修改为Python。

UPDATE examination_info
SET tag ='Python' WHERE tag='PYTHON'

SQL5 更新记录（二）

把exam_record表中2021年9月1日之前开始作答的未完成记录全部改为被动完成，即：将完成时间改为'2099-01-01 00:00:00'，分数改为 0。

UPDATE exam_record
SET submit_time='2099-01-01 00:00:00', score=0
WHERE start_time<'2021-09-01' AND submit_time is null

删除记录

SQL6 删除记录（一）

删除exam_record表中作答时间小于5分钟整且分数不及格（及格线为60分）的记录。

DELETE FROM exam_record
WHERE timestampdiff(minute,start_time,submit_time) < 5 AND score < 60

SQL7 删除记录（二）

删除exam_record表中未完成作答或作答时间小于5分钟整的记录中，开始作答时间最早的3条记录。

DELETE FROM exam_record
WHERE timestampdiff(minute, start_time, submit_time) < 5
OR submit_time IS null
ORDER BY start_time
LIMIT 3

SQL8 删除记录（三）

删除exam_record表中所有记录，并重置自增主键。

TRUNCATE TABLE exam_record;

02 表与索引操作

表的创建、修改与删除

SQL9 创建一张新表

创建一张优质用户信息表user_info_vip，表结构和用户信息表一致。

CREATE TABLE IF NOT EXISTS user_info_vip (
    id int(11) primary key auto_increment comment '自增ID',
    uid int(11) unique not null comment '用户ID',
    nick_name varchar(64) comment '昵称',
    achievement int(11) default 0 comment '成就值',
    level int(11) comment '用户等级',
    job varchar(32) comment '职业方向',
    register_time datetime default current_timestamp comment '注册时间'
);

SQL10 修改表

在用户信息表，字段level的后面增加一列最多可保存15个汉字的字段school；并将表中job列名改为profession，同时varchar字段长度变为10；achievement的默认值设置为 0。

ALTER TABLE user_info ADD school varchar(15) AFTER LEVEL;
ALTER TABLE user_info CHANGE job profession varchar(10);
ALTER TABLE user_info MODIFY achievement int(11) DEFAULT 0;

SQL11 删除表

删掉（如果存在）（2011到2014年）备份表。

DROP TABLE
IF EXISTS
exam_record_2011,
exam_record_2012,
exam_record_2013,
exam_record_2014;

索引的创建、删除

SQL12 创建索引

为了对表更方便快捷地查询，需要在examination_info表创建以下索引，规则如下：

在duration列创建普通索引idx_duration、在exam_id列创建唯一性索引uniq_idx_exam_id、在tag列创建全文索引full_idx_tag。

# 唯一索引
ALTER TABLE examination_info
ADD UNIQUE INDEX uniq_idx_exam_id(exam_id);

# 全文索引
ALTER TABLE examination_info
ADD FULLTEXT INDEX full_idx_tag(tag);
 
# 普通索引
ALTER TABLE examination_info
ADD INDEX idx_duration(duration);

SQL13 删除索引

删除examination_info表上的唯一索引uniq_idx_exam_id和全文索引full_idx_tag。

DROP index uniq_idx_exam_id ON examination_info;
DROP index full_idx_tag ON examination_info;

03 聚合分组查询

聚合函数

SQL14 SQL类别高难度试卷得分的截断平均值

从exam_record数据表中计算所有用户完成SQL类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）。

SELECT tag,difficulty,
round((SUM(score) - MIN(score) - MAX(score)) / (COUNT(score) - 2), 1) as avg_score
FROM examination_info 
JOIN exam_record using(exam_id)
WHERE tag='SQL' AND difficulty='hard';

SQL15 统计作答次数

从表中统计出总作答次数total_pv、试卷已完成作答数complete_pv、已完成的试卷数complete_exam_cnt。

SELECT DISTINCT COUNT(*) as total_pv, COUNT(submit_time) as compelete_pv,
COUNT(DISTINCT exam_id AND score IS not NULL) as complete_exam_cnt
FROM exam_record;

SQL16 得分不小于平均分的最低分

从表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。

SELECT MIN(score) as min_score_over_avg
FROM examination_info as i 
JOIN exam_record as r
ON i.exam_id = r.exam_id
WHERE tag = 'SQL'
AND score >= (
    SELECT AVG(score) FROM examination_info as i
    JOIN exam_record as r
    ON i.exam_id = r.exam_id
    WHERE tag = 'SQL'
);

分组查询

SQL17 平均活跃天数和月活人数

计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau。

SELECT concat(substr(submit_time, 1, 4), substr(submit_time, 6, 2)) as month,
ROUND(COUNT(DISTINCT uid, day(submit_time)) / COUNT(DISTINCT uid), 2) as avg_active_days,
ROUND(COUNT(DISTINCT(uid)), 0) as mau
FROM exam_record
WHERE submit_time IS NOT null AND year(submit_time) = '2021'
GROUP BY month;

SQL18 月总刷题数和日均刷题数

统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt（按月份升序排序）以及该年的总体情况。

SELECT ifnull(a.ymd, '2021汇总'), COUNT(1), ROUND(COUNT(1) / max(da), 3)
FROM (
    SELECT DATE_FORMAT(submit_time, '%Y%m') ymd, 
    DAYOFMONTH(last_day(submit_time)) da
    FROM  practice_record 
    WHERE  year(submit_time) = '2021' 
) a
GROUP BY a.ymd WITH ROLLUP

SQL19 未完成试卷数大于1的有效用户

统计2021年每个未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷作答数至少为1且未完成数小于5），输出用户ID、未完成试卷作答数、完成试卷作答数、作答过的试卷tag集合，按未完成试卷数量由多到少排序。

SELECT uid, COUNT(incomplete) as incomplete_cnt,
    COUNT(complete) as complete_cnt,
    GROUP_CONCAT(DISTINCT concat_ws(':', date(start_time), tag) SEPARATOR ';') as detail
FROM (
    SELECT uid, tag, start_time,
        IF(submit_time is null, 1, null) as incomplete,
        IF(submit_time is null, null, 1) as complete
    FROM exam_record 
    LEFT JOIN examination_info USING(exam_id)
    WHERE year(start_time)=2021
) as exam_complete_rec
GROUP BY uid
HAVING complete_cnt >= 1 and incomplete_cnt BETWEEN 2 and 4
ORDER BY incomplete_cnt DESC

04 多表查询

嵌套子查询

SQL20 月均完成试卷数不小于3的用户爱作答的类别

统计出 “当月均完成试卷数”不小于3的用户们爱作答的类别及作答次数，按次数降序输出。

SELECT tag, COUNT(start_time) as tag_cnt
FROM exam_record er INNER JOIN examination_info ei
ON er.exam_id = ei.exam_id
WHERE uid IN
(SELECT uid FROM exam_record er GROUP BY uid, month(start_time)
HAVING COUNT(submit_time) >= 3)
GROUP BY tag
ORDER BY tag_cnt DESC

SQL21 试卷发布当天作答人数和平均分

计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，按人数降序，相同人数的按平均分升序。

SELECT exam_id, COUNT(DISTINCT uid) AS uv, ROUND(AVG( score ), 1) AS avg_score
FROM exam_record
WHERE (exam_id, DATE(start_time)) IN (
    SELECT exam_id, DATE(release_time)
    FROM examination_info WHERE tag = "SQL"
) AND uid IN (SELECT uid FROM user_info WHERE `level` > 5)
GROUP BY exam_id
ORDER BY uv DESC, avg_score ASC;

SQL22 作答试卷得分大于过80的人的用户等级分布

统计作答SQL类别的试卷得分大于过80的人的用户等级分布，按数量降序排序（保证数量都不同）。

SELECT level, COUNT(DISTINCT uid) AS level_cnt
FROM exam_record er JOIN user_info ui USING(uid)
JOIN examination_info ei USING(exam_id)
WHERE ei.tag='SQL' AND er.score > 80
GROUP BY level
ORDER BY COUNT(DISTINCT uid) DESC, level DESC

合并查询

SQL23 每个题目和每份试卷被作答的人数和次数

统计每个题目和每份试卷被作答的人数和次数，分别按照"试卷"和"题目"的uv & pv降序显示。

SELECT exam_id as tid, COUNT(DISTINCT uid) as uv, COUNT(uid) as pv
FROM exam_record
GROUP BY exam_id
UNION ALL
SELECT question_id as tid,
  COUNT(DISTINCT uid) as uv,
  COUNT(uid) as pv
FROM practice_record
GROUP BY question_id
ORDER BY LEFT(tid,1) DESC,uv DESC,pv DESC;

SQL24 分别满足两个活动的人

输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，按用户ID排序输出。

(SELECT r.uid, 'activity1' as activity
FROM exam_record r WHERE year(submit_time) = 2021 
GROUP BY r.uid HAVING MIN(score) >= 85) UNION ALL
(SELECT r.uid,'activity2' as activity 
FROM exam_record r LEFT JOIN examination_info i ON r.exam_id = i.exam_id
WHERE year(submit_time) = 2021 AND i.difficulty = 'hard' AND score >=80 AND timestampdiff(second,r.start_time,r.submit_time) <= i.duration * 30
GROUP BY r.uid) ORDER BY uid

连接查询

SQL25 满足条件的用户的试卷完成数和题目练习数

查询高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。

SELECT er1.uid as uid,
       COUNT(DISTINCT er1.exam_id) as exam_cnt,
       COUNT(DISTINCT pr1.id) as question_cnt
FROM exam_record er1
LEFT JOIN practice_record pr1 ON er1.uid = pr1.uid AND year(er1.submit_time)=2021 AND YEAR(pr1.submit_time)=2021
WHERE er1.uid IN (
    SELECT er.uid
FROM user_info ui
LEFT JOIN exam_record er ON ui.uid = er.uid
JOIN examination_info ei ON er.exam_id = ei.exam_id
WHERE ei.tag = 'SQL' AND ui.level = 7 AND difficulty = 'hard' AND year(submit_time)=2021
GROUP BY ui.uid
HAVING AVG(er.score) >80)
GROUP BY er1.uid
ORDER BY exam_cnt , question_cnt DESC

SQL26 每个6/7级用户活跃情况

统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序。

SELECT t1.uid, COUNT(DISTINCT act_month) act_month_total,
COUNT(DISTINCT CASE WHEN year(act_day)=2021 THEN act_day END) act_days_2021,
COUNT(DISTINCT CASE WHEN LEFT(tag,1)=9 AND year(act_day)=2021 THEN act_day END) act_days_2021_exam,
COUNT(DISTINCT CASE WHEN LEFT(tag,1)=8 AND year(act_day)=2021 THEN act_day END) act_days_2021_question
FROM user_info t1
LEFT JOIN (SELECT uid, date_format(start_time, '%Y%m') act_month, date_format(start_time, '%Y%m%d') act_day, exam_id tag
FROM exam_record
UNION ALL
SELECT uid,
      date_format(submit_time,'%Y%m') act_month,
      date_format(submit_time,'%Y%m%d') act_day,
      question_id tag
FROM practice_record) t2
ON t1.uid=t2.uid
WHERE t1.uid IN (SELECT uid 
FROM user_info
WHERE LEVEL IN (6,7))
GROUP BY uid
ORDER BY act_month_total DESC, act_days_2021 DESC

05 窗口函数

专用窗口函数

SQL27 每类试卷得分前3名

查询每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。

SELECT tag,uid,rk
FROM (
    SELECT tag, uid, rank() over (partition by tag ORDER BY tag, MAX(score) DESC, MIN(score) DESC, uid DESC) AS rk
    FROM exam_record er LEFT JOIN examination_info ei ON er.exam_id = ei.exam_id
    GROUP BY tag,uid
) AS t
WHERE rk <= 3

SQL28 第二快/慢用时之差大于试卷时长一半的试卷

查询第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序。

SELECT DISTINCT exam_id, duration, release_time
FROM 
(SELECT exam_id, duration, release_time,
 SUM(CASE WHEN rank1= 2 THEN costtime WHEN rank2= 2 THEN -costtime ELSE 0 END ) AS sub
 FROM(
SELECT exam_id,duration, release_time,
TIMESTAMPDIFF(minute,start_time, submit_time) AS costtime,
row_number() over (PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(minute,start_time, submit_time) DESC) as rank1,
row_number() over (PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(minute,start_time, submit_time) ASC)  as rank2
FROM exam_record
JOIN examination_info USING (exam_id)
WHERE submit_time IS NOT null ) a 
GROUP BY exam_id ) b 
WHERE sub * 2 >= duration
ORDER BY exam_id DESC

SQL29 连续两次作答试卷的最大时间窗

计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序。


SELECT uid, MAX(day1)+1 days_window,
round((COUNT(*) / (SUM(day1) + 1)) * (MAX(day1) + 1), 2) avg_exam_cnt
FROM(
SELECT uid,date1,lag1,datediff(date1,lag1) day1
FROM(
SELECT uid,date(start_time) date1,
lag(date(start_time),1,date(start_time)) 
over(PARTITION BY uid ORDER BY start_time) lag1
FROM exam_record WHERE uid IN(
SELECT uid FROM exam_record WHERE year(start_time)=2021
GROUP BY uid HAVING COUNT(DISTINCT date(start_time)) > 1) AND year(start_time)=2021) a) b
GROUP BY uid
ORDER BY days_window DESC, avg_exam_cnt DESC

SQL30 近三个月未完成试卷数为0的用户完成情况

查询每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名。

SELECT a.uid, COUNT(a.start_time) AS exam_complete_cnt
FROM (SELECT uid, start_time, submit_time, DENSE_RANK() over(PARTITION BY uid ORDER BY date_format(start_time, '%Y%m') DESC) AS time_rank
      FROM exam_record) AS a
WHERE a.time_rank <= 3 GROUP BY a.uid HAVING COUNT(a.start_time) = COUNT(a.submit_time)
ORDER BY exam_complete_cnt DESC, uid DESC

SQL31 未完成率较高的50%用户近三个月答卷情况

查询SQL试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目。按用户ID、月份升序排序。

# 第一步，先找出未完成率前50%高的用户ID，注意这里需要的sql试卷
WITH rote_tab AS
(SELECT t.uid, t.f_rote, row_number() over(ORDER BY t.f_rote DESC, uid) AS rank2, COUNT(t.uid)over(PARTITION BY t.tag) AS cnt
FROM (SELECT er.uid, ef.tag, (SUM(IF(submit_time IS null, 1, 0)) / COUNT(start_time)) AS f_rote
FROM exam_record er LEFT JOIN examination_info ef ON ef.exam_id=er.exam_id WHERE tag='SQL' GROUP BY uid ) t)
# 第四步，分用户和月份进行数据统计；同时需要注意，统计的试卷数是所有类型的，不是之前仅有SQL类型
SELECT uid, start_month, COUNT(start_time) AS total_cnt, COUNT(submit_time) AS complete_cnt
FROM (
# 第三步，利用窗口函数对每个用户的月份进行降序排序，以便找出最近的三个月；
SELECT uid, start_time, submit_time, date_format(start_time,'%Y%m') AS start_month, dense_rank()over(PARTITION BY uid ORDER BY date_format(start_time,'%Y%m') DESC) AS rank3
FROM exam_record 
WHERE uid IN (
    SELECT DISTINCT er.uid
    FROM exam_record er LEFT JOIN user_info uf ON uf.uid=er.uid
    WHERE er.uid IN (SELECT uid FROM rote_tab WHERE rank2 <= round(cnt / 2, 0))
    AND uf.level IN (6, 7)) # 第二步，进一步找出满足等级为6或7的用户ID
) t2 WHERE rank3 <= 3 GROUP BY uid,start_month ORDER BY uid,start_month

SQL32 试卷完成数同比2020年的增长率及排名变化

计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出。

SELECT tag, exam_cnt_20, exam_cnt_21, growth_rate, exam_cnt_rank_20, exam_cnt_rank_21, Cast(exam_cnt_rank_21 AS signed) - Cast(exam_cnt_rank_20 AS signed) AS rank_delta # signed为整型默认属性
FROM (
    SELECT tag, exam_cnt_20, exam_cnt_21,
    Concat(Round((exam_cnt_21- exam_cnt_20) / exam_cnt_20 * 100, 1), '%') AS growth_rate,
    Rank() Over(ORDER BY exam_cnt_20 DESC) AS exam_cnt_rank_20,
    Rank() Over(ORDER BY exam_cnt_21 DESC) AS exam_cnt_rank_21
      FROM (SELECT tag, COUNT(CASE WHEN Date(submit_time) BETWEEN '2020-01-01' AND '2020-06-30' THEN 1 ELSE Null END) AS exam_cnt_20,
            COUNT(CASE WHEN Date(submit_time) BETWEEN '2021-01-01' AND '2021-06-30' THEN 1 ELSE Null END) AS exam_cnt_21
            FROM exam_record JOIN examination_info USING(exam_id) GROUP BY tag
           ) AS d
) AS u
WHERE exam_cnt_20 != 0 AND exam_cnt_21 != 0 ORDER BY growth_rate DESC, exam_cnt_rank_21 DESC;

聚合窗口函数

SQL33 对试卷得分做min-max归一化

在物理学及统计学数据计算时，有个概念叫min-max标准化，也被称为离差标准化，是对原始数据的线性变换，使结果值映射到[0 - 1]之间。

转换函数为：

请你将用户作答高难度试卷的得分在每份试卷作答记录内执行min-max归一化后缩放到[0,100]区间，并输出用户ID、试卷ID、归一化后分数平均值；最后按照试卷ID升序、归一化分数降序输出。（注：得分区间默认为[0,100]，如果某个试卷作答记录中只有一个得分，那么无需使用公式，归一化并缩放后分数仍为原分数）。

SELECT uid, exam_id, round(AVG(newscore), 0) avg_new_score
FROM (SELECT uid, exam_id, IF(maxs != mins, (score - mins) * 100 / (maxs - mins), maxs) newscore
     FROM (SELECT uid, exam_record.exam_id, score,
           MAX(score) over(
                  PARTITION BY exam_record.exam_id
              ) maxs,
           MIN(score) over(
               PARTITION BY exam_record.exam_id
           ) mins
           FROM exam_record RIGHT JOIN examination_info ON exam_record.exam_id = examination_info.exam_id
          WHERE score IS NOT null AND difficulty = 'hard'
         ) mai)new
GROUP BY uid, exam_id ORDER BY exam_id, avg_new_score DESC

SQL34 每份试卷每月作答数和截止当月的作答总数。

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

SELECT exam_id, DATE_FORMAT(start_time,'%Y%m') start_month, 
COUNT(DATE_FORMAT(start_time,'%Y%m')) AS month_cnt, 
SUM(COUNT(DATE_FORMAT(start_time,'%Y%m'))) over (PARTITION BY exam_id ORDER BY DATE_FORMAT(start_time,'%Y%m'))
FROM exam_record
GROUP BY exam_id, start_month
ORDER BY exam_id, start_month;

SQL35 每月及截止当月的答题情况

输出自从有用户作答记录以来，每月的试卷作答记录中月活用户数、新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数。结果按月份升序输出。

SELECT stime, mau, IF(month_add IS NOT null, month_add, 0) AS month_add_uv,
MAX(IF(month_add IS NOT null, month_add, 0)) over (ORDER BY stime) AS max_month_add_uv,
SUM(IF(month_add IS NOT null, month_add, 0)) over (ORDER BY stime) AS cum_sum_uv
FROM (SELECT stime, COUNT(DISTINCT uid) AS mau FROM
      (SELECT uid, DATE_FORMAT(start_time, '%Y%m') AS stime FROM exam_record) a
      GROUP BY stime) c LEFT JOIN (SELECT ntime, COUNT(distinct uid) AS month_add FROM
         (SELECT uid, MIN(DATE_FORMAT(start_time,'%Y%m')) AS ntime from exam_record GROUP BY uid) b
                                   GROUP BY ntime) d ON c.stime=d.ntime

06 其他常用操作

空值处理

SQL36 统计有未完成状态的试卷的未完成数和未完成率

查询未完成状态的试卷的未完成数incomplete_cnt和未完成率incomplete_rate。

SELECT exam_id, SUM(CASE WHEN score IS null THEN 1 ELSE 0 END) incomplete_cnt,
round((COUNT(*) - COUNT(submit_time)) / count(*), 3) incomplete_rate 
FROM exam_record
GROUP BY exam_id
HAVING (COUNT(*) - COUNT(submit_time)) <> 0

SQL37 0级用户高难度试卷的平均用时和平均得分

查询每个0级用户所有的高难度试卷考试平均用时和平均得分，未完成的默认试卷最大考试时长和0分处理。

# 每个0级用户所有的高难度试卷考试平均用时和平均得分，未完成的默认试卷最大考试时长和0分处理。
SELECT ui.uid, ROUND(AVG(IFNULL(score, 0)), 0),
    ROUND(AVG(IFNULL(TIMESTAMPDIFF(minute, start_time, submit_time), ei.duration)), 1)
FROM user_info ui
LEFT JOIN exam_record er USING(uid)
LEFT JOIN examination_info ei USING(exam_id)
WHERE level = 0 AND difficulty = 'hard' GROUP BY ui.uid

高级条件语句

SQL38 筛选限定昵称成就值活跃日期的用户

查询昵称以『牛牛』开头『号』结尾、成就值在1200~2500之间，且最近一次活跃（答题或作答试卷）在2021年9月的用户信息。

SELECT uid, nick_name, achievement
FROM(
    SELECT DISTINCT A.uid, nick_name, achievement, 
    MAX(date_format(start_time, "%y%m")) over(PARTITION BY uid ORDER BY uid) latest_active
    FROM (
        SELECT uid, start_time FROM exam_record
        UNION ALL
        SELECT uid, submit_time start_time
        FROM practice_record
    ) A, user_info ui
    WHERE A.uid = ui.uid AND substring(nick_name,1,2) = '牛牛' AND achievement>=1200 AND achievement <= 2500
) X WHERE latest_active = 2109

SQL39 筛选昵称规则和试卷规则的作答记录

查询昵称以"牛牛"+纯数字+"号"或者纯数字组成的用户对于字母c开头的试卷类别（如C,C++,c#等）的已完成的试卷ID和平均得分，按用户ID、平均分升序排序。

SELECT u_i.uid AS uid, e_r.exam_id AS exam_id, round(AVG(score), 0) AS avg_score
FROM exam_record e_r JOIN examination_info e_i ON e_r.exam_id = e_i.exam_id
JOIN user_info u_i ON e_r.uid = u_i.uid
WHERE score IS NOT null AND tag RLIKE '^(C|c).*'AND (
    nick_name RLIKE '^[0-9]+$' OR nick_name RLIKE '^牛牛[0-9]+号$' 
) GROUP BY uid, exam_id ORDER BY uid, avg_score

SQL40 根据指定记录是否存在输出不同情况

查询表中的数据，当有任意一个0级用户未完成试卷数大于2时，输出每个0级用户的试卷未完成数和未完成率（保留3位小数）；若不存在这样的用户，则输出所有有作答记录的用户的这两个指标。结果按未完成率升序排序。

WITH t1 AS (
    SELECT user_info.uid, level, SUM(IF(submit_time IS NULL AND start_time IS NOT NULL, 1, 0)) incomplete_cnt,
    ROUND(IFNULL(SUM(IF(submit_time IS NULL AND start_time IS NOT NULL,1,0)) / COUNT(start_time), 0), 3) incomplete_rate,
    COUNT(start_time) num # 记录总作答次数，主要为了下面过滤输出所有有作答记录的用户。
    FROM user_info LEFT JOIN exam_record
    ON user_info.uid=exam_record.uid
    GROUP BY user_info.uid
)
SELECT t1.uid, incomplete_cnt, incomplete_rate # t1有所有用户的信息，是个全表
FROM t1
WHERE EXISTS(SELECT * FROM t1 WHERE incomplete_cnt > 2 AND level = 0) AND level = 0
UNION
SELECT t1.uid, incomplete_cnt, incomplete_rate
FROM t1
WHERE NOT EXISTS(SELECT * FROM t1 WHERE incomplete_cnt > 2 AND level = 0) AND num > 0
ORDER BY incomplete_rate;

SQL41 各用户等级的不同得分表现占比

为了得到用户试卷作答的定性表现，我们将试卷得分按分界点[90, 75, 60]分为优良中差四个得分等级（分界点划分到左区间），请查询不同用户等级的人在完成过的试卷中各得分等级占比（结果保留3位小数），未完成过试卷的用户无需输出，结果按用户等级降序、占比降序排序。

WITH tmp2 AS (
    SELECT ui.level, tmp.score_grade, COUNT(tmp.uid) AS num FROM
    (SELECT uid, exam_id, (
        CASE WHEN score >= 90 THEN '优'
        WHEN score<90 AND score >= 75 THEN '良'
        WHEN score<75 AND score >= 60 THEN '中'
        ELSE '差' END)as score_grade
     FROM exam_record WHERE submit_time IS NOT null
    ) tmp
    LEFT JOIN user_info ui ON tmp.uid = ui.uid GROUP BY ui.level, tmp.score_grade
) SELECT tmp2.level AS level, tmp2.score_grade, round(tmp2.num / tmp3.total_num, 3) AS ratio
FROM tmp2 LEFT JOIN (
    SELECT level, SUM(num) AS total_num FROM tmp2 GROUP BY level
) tmp3
ON tmp2.level = tmp3.level ORDER BY level DESC, ratio DESC

限量查询

SQL42 注册时间最早的三个人

查询注册时间最早的3个人。

SELECT  uid, nick_name, register_time FROM user_info ORDER BY register_time LIMIT 3;

SQL43 注册当天就完成了试卷的名单第三页

查询求职方向为算法工程师，且注册当天就完成了算法类试卷的人，按参加过的所有考试最高得分排名。排名榜很长，我们将采用分页展示，每页3条，现在需要你取出第3页（页码从1开始）的人的信息。

SELECT a.uid, b.level, b.register_time, MAX(a.score) AS max_score
FROM exam_record a INNER JOIN user_info b ON a.uid=b.uid INNER JOIN examination_info c ON a.exam_id=c.exam_id
GROUP BY a.uid, b.level, b.register_time
HAVING a.uid IN(SELECT a.uid FROM exam_record a
INNER JOIN user_info b ON a.uid=b.uid
INNER JOIN examination_info c ON a.exam_id=c.exam_id
WHERE b.job='算法' AND c.tag='算法' AND DATE_FORMAT(a.submit_time, '%Y%m%d')=DATE_FORMAT(b.register_time, '%Y%m%d'))
ORDER BY max_score DESC LIMIT 6, 3

文本转换函数

SQL44 修复串列了的记录

因手误将部分记录的试题类别tag、难度、时长同时录入到了tag字段，请查询出这些录错了的记录，并拆分后按正确的列类型输出。

SELECT
exam_id, substring_index(tag, ',', 1) tag,
substring_index(substring_index(tag, ',', 2), ',', -1) difficulty,
substring_index(tag, ',', -1) duration
FROM
examination_info
WHERE
difficulty=''

SQL45 对过长的昵称截取处理

有的用户的昵称特别长，在一些展示场景会导致样式混乱，因此需要将特别长的昵称转换一下再输出，请输出字符数大于10的用户信息，对于字符数大于13的用户输出前10个字符然后加上三个点号：『...』。

SELECT uid, IF(
   CHAR_LENGTH(nick_name) > 13, CONCAT(LEFT(nick_name, 10), "..."), nick_name
) nick_name
FROM user_info
WHERE CHAR_LENGTH(nick_name) > 10

SQL46 大小写混乱时的筛选统计

试卷的类别tag可能出现大小写混乱的情况，请先筛选出试卷作答数小于3的类别tag，统计将其转换为大写后对应的原本试卷作答数。如果转换后tag并没有发生变化，不输出该条结果。

WITH t AS (
    SELECT tag, COUNT(er.start_time) AS answer_cnt
    FROM examination_info ei
    RIGHT JOIN exam_record er
    USING (exam_id)
    GROUP BY tag
)
SELECT t.tag, t1.answer_cnt
FROM t
INNER JOIN t t1 ON upper(t.tag) = t1.tag AND t.tag != t1.tag
WHERE t.answer_cnt < 3

withoutfear

关注

2
点赞
踩
11

收藏

觉得还不错? 一键收藏
打赏
0
评论
SQL 进阶挑战篇

01增删改操作插入记录SQL1 插入记录（一）现在有两个用户的作答记录详情如下：用户1001在2021年9月1日晚上10点11分12秒开始作答试卷9001，并在50分钟后提交，得了90分；用户1002在2021年9月4日上午7点1分2秒开始作答试卷9002，并在10分钟后退出了平台。请用一条语句将这两条记录插入表中。INSERT INTO exam_record VALUES(null, 1001, 9001, '2021-09-01 22:11:12', '2021-09-.
复制链接

扫一扫