排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2026-01-05 小结-行转列，展开学生成绩（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
where
student_id = '460093'
group by
exam_date
2026-01-05 HAVING-语数英优异的学生  
SELECT student_id, SUM(score) AS total_score
FROM scores
WHERE subject IN ('语文', '数学', '英语') AND exam_date = '2024-06-30'
GROUP BY student_id
HAVING SUM(score) > 330;
2026-01-05 HAVING-执教教师超过3人的科目  
select 
subject
from 
teachers
group by
subject
having
count(teacher_id) >= 3
2026-01-05 HAVING-每次成绩都不低于80分的学生  
select 
student_id,
max(score) max_score, 
min(score) min_score, 
avg(score) avg_score 
from 
scores
group by
student_id
having
min(score) >= 80
2026-01-05 CASE WHEN-老中青教师数量  
select 
case when enter_date < '2000-01-01' then '资深教师'
 when enter_date >= '2010-01-01' then '青年教师'
 else '中年教师'
end as teacher_type,
count(*) as teacher_count
from
teachers
group by
teacher_type
2026-01-05 CASE WHEN-男女学生的数量  
select 
CASE 
        WHEN gender = 'm' THEN '男'
        WHEN gender = 'f' THEN '女'
    END AS gender_text,
count(*) as student_count
from 
students
group by
gender
2026-01-05 聚合函数-比较两位同学的数学成绩  
select 
student_id,
max(score) as max_score,
min(score) as min_score,
avg(score) as avg_score
from 
scores 
where
(student_id = '460093 ' or student_id = '735011 ')
and
 subject = '数学'
group by
 student_id
2026-01-05 聚合函数-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 = '语文'
2026-01-05 GROUP BY-年龄最大学生的出生日期  
select 
class_code,
min(birth_date) as min_birth_date
from 
students
group by
class_code
order by
class_code asc
2026-01-05 GROUP BY-各科目最高分、最低分  
select 
subject,
max(score) as max_score,
min(score) as min_score
from 
scores 
group by
subject
2026-01-05 GROUP BY-各科目平均分  
select 
subject,
avg(score) as average_score
from 
scores 
where
exam_date = '2024-06-30'
group by
subject
2026-01-05 GROUP BY-各科目平均分  
select 
subject,
sum(score) / count(student_id) as average_score
from 
scores 
where
exam_date = '2024-06-30'
group by
subject
2026-01-05 GROUP BY-各班级人数  
select 
class_code,
count(student_id) as student_count
from 
students
group by
class_code
order by
count(student_id) desc
2026-01-05 条件过滤-没有职称的老教师  
select 
name ,
subject,
class_code,
enter_date
from 
teachers 
where
enter_date < '2010-01-01'
and
qualification is null
order by
enter_date asc
2026-01-04 条件过滤-查找1994年至1997年毕业的女教师  
select 
name,
subject,
class_code,
graduate_date
from 
teachers
where
(graduate_date between '1994-01-01' and '1997-12-31')
and
gender = 'f'
2026-01-04 条件过滤-符合条件的班主任  
select 
name, 
subject, 
class_code, 
qualification 
from 
teachers 
where
(fir_degr= '清华大学' orfir_degr = '北京大学')
and
head_teacher is not null
order by
name asc
2026-01-03 条件过滤-找出所有教授数学且具有高级职称的教师  
select 
name, 
subject, 
class_code, 
qualification
from 
teachers
where 
subject = '数学'
and
qualification ='Senior'
ORDER BY 
name ASC;
2026-01-03 条件过滤-找出所有教授数学且具有高级职称的教师  
select 
* 
from 
teachers
where 
subject = '数学'
and
qualification ='Senior'
ORDER BY 
name ASC;
2026-01-03 条件过滤-查找2009年出生的女学生  
select 
student_id,
name,
birth_date
from 
students
where
year(birth_date) = '2009'
and
gender = 'f'
order by
birth_date asc
2026-01-03 条件过滤-查找2009年出生的女学生  
select 
student_id,
name,
birth_date
from 
students
where
year(birth_date) = '2009'
and
gender = 'm'
order by
birth_date asc