排名

用户解题统计

过去一年提交了

勋章 ①金银铜:在竞赛中获得第一二三名;②好习惯:自然月10天提交;③里程碑:解决1/2/5/10/20/50/100/200题;④每周打卡挑战:完成每周5题,每年1月1日清零。

收藏

收藏日期 题目名称 解决状态
没有收藏的题目。

评论笔记

评论日期 题目名称 评论内容 站长评论
没有评论过的题目。

提交记录

提交日期 题目名称 提交代码
2024-12-01 聚合函数-比较两位同学的数学成绩 
SELECT 
    student_id,
    MAX(score) AS max_score, 
    MIN(score) AS min_score, 
    AVG(score) AS avg_score
FROM scores
WHERE student_id IN (460093, 735011) AND subject = '数学'
GROUP BY student_id
2024-12-01 聚合函数-比较两位同学的数学成绩 
SELECT
student_id,
MAX(score) AS max_score,
MIN(score) AS min_score,
AVG(score) AS AVG_score
FROM scores 
WHERE student_id='460093'
AND subject='数学'
union
SELECT 
student_id,
MAX(score) AS max_score,
MIN(score) AS min_score,
AVG(score) AS AVG_score
FROM scores 
WHERE student_id='735011'
AND subject='数学'
2024-12-01 聚合函数-735011学生的语文成绩 
SELECT 
max(score) AS max_score,
min(score) AS min_score,
AVG(score) AS avg_score
FROM scores
WHERE student_id='735011'
AND subject='语文'
2024-12-01 GROUP BY-年龄最大学生的出生日期 
SELECT 
class_code,
min(birth_date) AS min_birth_date
FROM students
GROUP BY class_code
ORDER BY class_code
2024-12-01 GROUP BY-各班级人数 
SELECT 
class_code,
COUNT(student_id) AS student_count
FROM students 
GROUP BY class_code
HAVING COUNT(student_id)>10
ORDER BY class_code DESC
2024-12-01 GROUP BY-各班级人数 
SELECT 
class_code,
COUNT(student_id) AS student_count
FROM students 
GROUP BY class_code
HAVING COUNT(student_id)>10
ORDER BY class_code
2024-12-01 GROUP BY-各班级人数 
SELECT 
class_code,
COUNT(student_id) AS student_count
FROM students 
GROUP BY grade_code,class_code
HAVING COUNT(student_id)>10
ORDER BY class_code
2024-12-01 GROUP BY-各班级人数 
SELECT 
class_code,
COUNT(student_id) AS student_count
FROM students 
GROUP BY grade_code,class_code
ORDER BY class_code
2024-12-01 GROUP BY-各班级人数 
SELECT 
class_code,
COUNT(student_id) AS student_count
FROM students 
GROUP BY class_code
ORDER BY class_code
2024-11-30 HAVING-每次成绩都不低于80分的学生 
SELECT 
student_id
FROM scores 
GROUP BY student_id
HAVING IFNULL(MIN(score),80)>=80
2024-11-30 CASE WHEN-老中青教师数量 
SELECT 
	CASE WHEN YEAR(enter_date)>=2010 THEN '青年教师'
WHEN YEAR(enter_date)<2000 THEN '资深教师'
ELSE '中年教师' END AS teacher_type,
COUNT(teacher_id) AS teacher_count
FROM teachers
GROUP BY teacher_type
2024-11-30 CASE WHEN-男女学生的数量 
SELECT 
CASE WHEN gender='m' THEN '男' ELSE '女' END AS gender_text,
COUNT(student_id) AS student_count
FROM students 
GROUP BY gender_text
2024-11-30 HAVING-语数英优异的学生 
SELECT 
student_id,
MAX(CASE WHEN subject='语文' THEN score ELSE 0 END)+
MAX(CASE WHEN subject='数学' THEN score ELSE 0 END)+
MAX(CASE WHEN subject='英语' THEN score ELSE 0 END) AS total_score
FROM scores 
WHERE exam_date='2024-06-30' AND subject IN('语文','数学','英语')
GROUP BY student_id
HAVING total_score>330
2024-11-30 HAVING-执教教师超过3人的科目 
SELECT 
subject
FROM teachers
GROUP BY subject 
HAVING COUNT(teacher_id)>=3
2024-11-30 小结-行转列,展开学生成绩(1) 
SELECT 
exam_date,
    MAX(CASE WHEN subject = '语文' THEN score ELSE NULL END) AS chinese_score,
    MAX(CASE WHEN subject = '数学' THEN score ELSE NULL END) AS math_score,
    MAX(CASE WHEN subject = '英语' THEN score ELSE NULL END) AS english_score
FROM scores 
LEFT JOIN students USING(student_id) 
WHERE student_id='460093'
AND subject IN ('语文','数学','英语')
GROUP BY exam_date
ORDER BY exam_date ASC
2024-11-30 GROUP BY-各科目最高分、最低分 
SELECT
subject,
max(score) AS max_score,
min(score) AS min_score
FROM scores
GROUP BY subject
ORDER BY subject
2024-11-30 GROUP BY-各科目平均分 
SELECT 
subject,
AVG(score) AS average_score
FROM scores
WHERE exam_date='2024-06-30'
GROUP BY subject
ORDER BY subject ASC
2024-11-30 GROUP BY-各科目平均分 
SELECT 
subject,
AVG(score) AS avg_score
FROM scores
WHERE exam_date='2024-06-30'
GROUP BY subject
ORDER BY subject ASC
2024-11-30 GROUP BY-各科目平均分 
SELECT 
subject,
AVG(score) AS avg_score
FROM scores
WHERE exam_date='2024-06-30'
GROUP BY subject
ORDER BY subject
2024-11-30 GROUP BY-各科目平均分 
SELECT 
subject,
AVG(score) AS avg_score
FROM scores
WHERE exam_date='2024-06-30'
GROUP BY subject