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;