文章目录
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进阶挑战 03 聚合分组查询
聚合函数
SQL 123 SQL类别高难度试卷得分的截断平均值
SELECT
t1.tag,
t1.difficulty,
ROUND(
(SUM(score) - MAX(score) - MIN(score)) / (COUNT(score) -2),
1
) clip_avg_score
FROM
examination_info t1
JOIN exam_record t2 ON t1.exam_id = t2.exam_id
WHERE
t1.tag = 'SQL'
AND t1.difficulty = 'hard';
SQL 124 统计作答次数
SELECT
COUNT(id) total_pv,
COUNT(submit_time) complete_pv,
COUNT(DISTINCT IF(submit_time IS NOT NULL, exam_id, NULL)) complete_exam_cnt
FROM
exam_record;
SQL 125 得分不小于平均分的最低分
SELECT MIN(score) min_score_over_avg
FROM exam_record
WHERE exam_id IN (SELECT exam_id FROM examination_info WHERE tag = 'SQL')
AND score >=
(
SELECT AVG(score) avg_score
FROM exam_record
WHERE exam_id IN (
SELECT exam_id FROM examination_info WHERE tag = 'SQL'
)
);
分组查询
SQL 126 平均活跃天数和月活人数
SELECT
DATE_FORMAT(submit_time, '%Y%m') `month`,
ROUND((
COUNT(DISTINCT uid, DATE(submit_time)) / COUNT(DISTINCT uid)
),2) avg_active_days,
COUNT(DISTINCT uid) mau
FROM
exam_record
WHERE
submit_time IS NOT NULL
AND DATE_FORMAT(submit_time, '%Y') = 2021
GROUP BY
DATE_FORMAT(submit_time, '%Y%m')
ORDER BY
`month` ASC;
SQL 127 月总刷题数和日均刷题数
SELECT
DATE_FORMAT(submit_time, '%Y%m') submit_month,
COUNT(*) month_q_cnt,
ROUND(COUNT(*) / AVG(DAY(LAST_DAY(submit_time))), 3) avg_day_q_cnt
FROM
practice_record
WHERE
YEAR(submit_time) = '2021'
AND submit_time IS NOT NULL
GROUP BY
DATE_FORMAT(submit_time, '%Y%m')
UNION ALL
SELECT
'2021汇总' submit_month,
COUNT(*) month_q_cnt,
ROUND(COUNT(*) / 31, 3) avg_day_q_cnt
FROM
practice_record
WHERE
YEAR(submit_time) = '2021'
AND submit_time IS NOT NULL
ORDER BY
submit_month;
SQL 128 未完成试卷数大于1的有效用户
方案一
SELECT t1.uid,t1.incomplete_cnt,t1.complete_cnt,t2.detail
FROM (
SELECT
uid,
SUM(IF(submit_time IS NULL,1,0)) incomplete_cnt,
COUNT(submit_time) complete_cnt
FROM
exam_record
WHERE
YEAR(start_time)=2021
GROUP BY
uid
HAVING
COUNT(submit_time) >= 1
AND SUM(IF(submit_time IS NULL,1,0)) > 1
AND SUM(IF(submit_time IS NULL,1,0)) < 5
) t1,(
SELECT
uid,
GROUP_CONCAT(
DISTINCT CONCAT(DATE(start_time),':',tag)
ORDER BY start_time
SEPARATOR ';') detail
FROM
exam_record t1
JOIN examination_info t2
ON t1.exam_id = t2.exam_id
WHERE
YEAR(start_time) = 2021
GROUP BY
uid
) t2
WHERE
t1.uid = t2.uid
ORDER BY
t1.incomplete_cnt DESC;
方案二
SELECT
uid,
SUM(IF(submit_time IS NULL, 1, 0)) incomplete_cnt,
COUNT(submit_time) complete_cnt,
GROUP_CONCAT(
DISTINCT CONCAT(DATE(start_time), ':', tag)
ORDER BY start_time
SEPARATOR ';') detail
FROM
exam_record t1
JOIN examination_info t2 ON t1.exam_id = t2.exam_id
WHERE
YEAR(start_time) = 2021
GROUP BY
uid
HAVING
COUNT(submit_time) >= 1
AND SUM(IF(submit_time IS NULL, 1, 0)) > 1
AND SUM(IF(submit_time IS NULL, 1, 0)) < 5
ORDER BY
incomplete_cnt DESC;