【MySQL】【牛客-SQL进阶挑战】06 其他常用操作

---

SQL题解汇总

https://blog.csdn.net/guliguliguliguli/article/details/126109166

题目链接

https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=240

空值处理

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

SELECT exam_id,
    SUM(IF(score IS NULL,1,0)) incomplete_cnt,
    ROUND(SUM(IF(score IS NULL,1,0))/COUNT(exam_id),3) complete_rate
FROM exam_record
GROUP BY exam_id
HAVING incomplete_cnt > 0;

SELECT exam_id,
    COUNT(start_time)-COUNT(submit_time) incomplete_cnt,
    ROUND((COUNT(start_time)-COUNT(submit_time)) / COUNT(start_time),3) complete_rate
FROM exam_record
GROUP BY exam_id
HAVING incomplete_cnt > 0;

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

SELECT t1.uid, 
    ROUND(AVG(IF(score IS NOT NULL,score,0))) avg_score, 
    ROUND(
        AVG(
            IF(submit_time IS NOT NULL,
            TIMESTAMPDIFF(MINUTE,start_time,submit_time),
            t3.duration)
        ),1) avg_time_took
FROM exam_record t1
JOIN (
    SELECT uid
    FROM user_info
    WHERE `level` = 0
) t2 ON t1.uid = t2.uid
JOIN (
    SELECT exam_id,duration
    FROM examination_info
    WHERE difficulty = 'hard'
) t3 ON t1.exam_id = t3.exam_id
GROUP BY t1.uid;

高级条件语句

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

SELECT t1.uid,t2.nick_name,t2.achievement
FROM exam_record t1
JOIN (
    SELECT uid,nick_name,achievement
    FROM user_info
    WHERE nick_name LIKE '牛客%号' 
        AND achievement>=1200 
        AND achievement<=2500
) t2 ON t1.uid = t2.uid
GROUP BY t1.uid
HAVING DATE_FORMAT(MAX(start_time),'%Y%m')='202109'

UNION

SELECT t1.uid,t2.nick_name,t2.achievement
FROM practice_record t1
JOIN (
    SELECT uid,nick_name,achievement
    FROM user_info
    WHERE nick_name LIKE '牛客%号' 
        AND achievement>=1200 
        AND achievement<=2500
) t2 ON t1.uid = t2.uid
GROUP BY t1.uid
HAVING DATE_FORMAT(MAX(submit_time),'%Y%m')='202109'

SELECT uid,nick_name,achievement
FROM user_info
WHERE nick_name LIKE '牛客%号'
    AND achievement BETWEEN 1200 AND 2500
    AND uid IN (
        SELECT uid
        FROM(
            SELECT uid,start_time act_time
            FROM exam_record
            UNION
            SELECT uid, submit_time act_time
            FROM practice_record
        ) t
        GROUP BY uid
        HAVING DATE_FORMAT(MAX(act_time),'%Y%m')='202109'
    )

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

SELECT t1.uid,t1.exam_id,ROUND(AVG(score)) avg_score
FROM exam_record t1
JOIN (
    SELECT uid
    FROM user_info
    WHERE nick_name RLIKE '^[0-9]+$' 
    OR nick_name RLIKE '^牛客[0-9]+号$'
) t2 ON t1.uid = t2.uid 
JOIN (
    SELECT exam_id
    FROM examination_info
    WHERE tag LIKE 'c%' OR tag LIKE 'C%'
) t3 ON t1.exam_id = t3.exam_id
WHERE score IS NOT NULL
GROUP BY uid,exam_id
ORDER BY uid ASC,avg_score ASC;

SELECT 
    t1.uid,
    t1.exam_id,
    ROUND(AVG(score)) avg_score
FROM 
    exam_record t1
JOIN user_info t2 ON t1.uid = t2.uid 
JOIN  examination_info t3 ON t1.exam_id = t3.exam_id
WHERE score IS NOT NULL
    AND (nick_name RLIKE '^[0-9]+$' OR nick_name RLIKE '^牛客[0-9]+号$')
    AND (tag LIKE 'c%' OR tag LIKE 'C%')
GROUP BY uid,exam_id
ORDER BY uid ASC,avg_score ASC;

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

题目要求：

• 当有任意一个0级用户未完成试卷数大于2时，输出每个0级用户的试卷未完成数和未完成率（保留3位小数）

• 若不存在这样的用户，则输出所有有作答记录的用户的这两个指标

使用EXISTS 和 NOT EXISTS可以解决这个问题

• 如果存在，则EXISTS成立，NOT EXISTS不成立

• 如果不存在，则NOT EXISTS成立，EXISTS不成立

不会有重复的数据出现

with t as(
    SELECT 
        t1.uid,
        t1.`level`,
        COUNT(start_time)-COUNT(score) incomplete_cnt,
        IFNULL(ROUND((COUNT(start_time)-COUNT(score)) / COUNT(start_time),3),0.000) incomplete_rate,
        COUNT(exam_id) num
    FROM user_info t1
    LEFT JOIN exam_record t2 ON t1.uid = t2.uid
    GROUP BY uid
)

SELECT uid,incomplete_cnt,incomplete_rate
FROM t 
WHERE EXISTS(
    SELECT uid FROM t WHERE `level`=0 AND incomplete_cnt > 2
) AND `level`=0

UNION ALL

SELECT uid,incomplete_cnt,incomplete_rate
FROM t 
WHERE NOT EXISTS(
    SELECT uid FROM t WHERE `level`=0 AND incomplete_cnt > 2
) AND num>0
ORDER BY incomplete_rate ASC;

SQL 150 各用户等级的不同得分表现占比（ over(partition by … ) ）

count(*) over(partition by level) as total

SELECT `level`,score_grade,ROUND(COUNT(score_grade)/total,3) ratio
FROM (
	SELECT `level`,
	    (CASE WHEN score>=90 THEN '优'
	     WHEN score>=75 AND score<90 THEN '良'
	     WHEN score>=60 AND score<75 THEN '中'
	     WHEN score<60 THEN '差'END
	    ) score_grade,
	    COUNT(*) over(PARTITION BY `level`) total
	FROM user_info t1
	JOIN exam_record t2 ON t1.uid = t2.uid
	WHERE score IS NOT NULL
) t
GROUP BY `level`,score_grade
ORDER BY `level` DESC,ratio DESC;

限量查询

SQL 151 注册时间最早的三个人

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

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

SELECT t1.uid,`level`,register_time,MAX(score) max_score
FROM exam_record t1
JOIN(
    SELECT uid,register_time,`level`
    FROM user_info
    WHERE job = '算法'
) t2 ON t1.uid = t2.uid
JOIN (
    SELECT exam_id
    FROM examination_info
    WHERE tag = '算法'
) t3 ON t1.exam_id = t3.exam_id
WHERE DATE(register_time) = DATE(submit_time)
GROUP BY t1.uid
ORDER BY max_score DESC
LIMIT 6,3;

文本转换函数

SQL 153 修复串列了的记录

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 = '';

SQL 154 对过长的昵称截取处理

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;

SELECT uid,
    IF(
    	CHAR_LENGTH(nick_name)>13,
    	CONCAT(SUBSTR(nick_name,1,10),'...'),
    	nick_name) nick_name
FROM user_info
WHERE CHAR_LENGTH(nick_name)>10;

SQL 155 大小写混乱时的筛选统计（困难）

WITH t AS(
    SELECT tag,COUNT(t1.exam_id) answer_cnt
    FROM exam_record t1
    JOIN examination_info t2 ON t1.exam_id = t2.exam_id
    GROUP BY tag
)

SELECT t1.tag,t2.answer_cnt
FROM t AS t1 JOIN t AS t2
ON UPPER(t1.tag) = t2.tag 
    AND t1.tag != t2.tag 
    AND t1.answer_cnt < 3;