---

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;
1
2
3
4
5
6

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;
1
2
3
4
5
6

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

限量查询

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

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

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

文本转换函数

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 = '';
1
2
3
4
5
6

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;
1
2
3
4
5
6
7

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;
1
2
3
4
5
6
7

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;
1
2
3
4
5
6
7
8
9
10
11
12