排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-07-01 优异物理成绩的分布 
SELECT 
 s.class_code, 
 COUNT(distinct s.student_id) AS num_students_90_plus, 
 ROUND(AVG(sc.score), 2) AS avg_score_90_plus, 
 t.name AS physics_teacher
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
JOIN teachers t ON t.class_code LIKE CONCAT('%', s.class_code, '%')
WHERE 
    sc.subject = '物理' 
    AND sc.score >= 90 and sc.exam_date='2024-06-30'
    AND t.subject = '物理'
GROUP BY 
    s.class_code, t.name
ORDER BY 
    avg_score_90_plus DESC;
2025-06-30 文科潜力股 
select * 
from scores 
where exam_date='2024-06-30' and subject in ('历史','政治','地理') and score>=90
order by score desc ,student_id,subject
2025-06-30 文科潜力股 
select * 
from scores 
where exam_date='2024-06-30' and subject in ('历史','政治','地理') and score>90
order by score desc ,student_id,subject
2025-06-30 给英语成绩中上水平的学生拔尖 
select * 
from scores 
where subject='英语' and score between '100' and '110' and exam_date='2024-06-30'
2025-06-30 给英语成绩中上水平的学生拔尖 
select * 
from scores 
where subject='英语' and score between '100' and '110'
2025-06-30 找出三个班级的女生 
select * 
from students 
where class_code in ('C219','C220','C221') and gender='f'
order by student_id
2025-06-30 语文数学英语至少1门超过100分的同学 
select * 
from subject_score 
where chinese>100 or math>100 or english>100
order by chinese
2025-06-27 小结-从不缺考的学生 
select distinct(student_id)
from scores
where score is not null
group by student_id
2025-06-27 小结-从不缺考的学生 
select distinct(student_id)
from scores
where score is null
2025-06-27 小结-行转列,展开学生成绩(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
2025-06-20 HAVING-语数英优异的学生 
select 
student_id,
sum(score)
from scores
where exam_date='2024-06-30' and subject in('语文','数学','英语')
group by student_id
having sum(score)>330
2025-06-20 HAVING-语数英优异的学生 
select 
student_id,
sum(score)
from scores
where exam_date='2024-06-30'
group by student_id
having sum(score)>330
2025-06-20 HAVING-执教教师超过3人的科目 
select
subject
from teachers
group by(subject)
having count(1)>=3
2025-06-20 HAVING-每次成绩都不低于80分的学生 
select 
student_id,
max(score),
min(score),
avg(score)
from scores
group by student_id
having min(score)>=80
2025-06-20 CASE WHEN-老中青教师数量 
select
case when enter_date>='2010-01-01' then '青年教师'
 when enter_date>='2000-01-01' then '资深教师'
 else '中年教师'
end as teacher_type,
count(*)
from teachers
group by teacher_type
2025-06-20 CASE WHEN-男女学生的数量 
select 
case when gender='m' then '男'
 when gender='f' then '女'
end as gender_text,
count(*) as gender_count
from students
group by gender
2025-06-19 聚合函数-比较两位同学的数学成绩 
select 
student_id,
max(score),
min(score),
avg(score)
from scores
where student_id in('460093','735011')and subject='数学'
group by student_id
2025-06-19 聚合函数-比较两位同学的数学成绩 
select 
max(score),
min(score),
avg(score)
from scores
where student_id in('460093','735011')and subject='数学'
group by student_id
2025-06-19 聚合函数-735011学生的语文成绩 
select 
max(score),
min(score),
avg(score)
from scores
where student_id='735011'and subject='语文'
2025-06-19 GROUP BY-年龄最大学生的出生日期 
select class_code,
min(birth_date)
from students
group by class_code
order by class_code