排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2025-03-30 优异物理成绩的分布  已解决

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-03-30 CASE WHEN-老中青教师数量  
select 
	case when enter_date >= '2010-01-01' then '青年教师'
	 	 when enter_date < '2000-01-01' then '资深教师'
 	 else '中年教师'
	end as teacher_type,
	count(*) as teacher_count
from teachers
GROUP BY teacher_type;
2025-03-30 CASE WHEN-男女学生的数量  
select 
	case 
	when gender = 'm' then '男'
	else '女' 
end as gender_text,
count(*) as student_count
from students
group by gender
2025-03-30 聚合函数-比较两位同学的数学成绩  
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
2025-03-30 聚合函数-735011学生的语文成绩  
select max(score) max_score , min(score) min_score , avg(score) avg_score 
from scores
where student_id = 735011 and subject = '语文'
2025-03-30 GROUP BY-年龄最大学生的出生日期  
select class_code , min(birth_date) as min_birth_date from 
students
group by class_code
order by class_code asc
2025-03-30 GROUP BY-各科目最高分、最低分  
select subject , max(score) max_score , min(score) asmin_score
from scores
group by subject
order by subject asc
2025-03-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
2025-03-30 GROUP BY-各班级人数  
selectclass_code , count(student_id) as student_count from
students
group by class_code
order by student_count desc
2025-03-30 条件过滤-没有职称的老教师  
select name , subject , class_code , enter_date
from teachers
where enter_date < '2010-01-01' and qualification is null
2025-03-30 字符串函数-查找任教3个班级的所有教师  
select name , subject , class_code
from teachers
where length(class_code) = 12
order by name
2025-03-30 字符串函数-查找任教5个班级的所有教师  
select name , subject , class_code from teachers 
where length(class_code) = 20
2025-03-30 条件过滤-查找1994年至1997年毕业的女教师  
select name , subject , class_code , graduate_date
from teachers
where graduate_date between '1994-01-01' and '1997-12-31'
order by graduate_date asc
2025-03-30 条件过滤-符合条件的班主任  
select	name , subject , class_code , qualification
from teachers
where (fir_degr = '北京大学' or '清华大学' )And head_teacher is not null
order by name asc
2025-03-30 条件过滤-符合条件的班主任  
select	name , subject , class_code , qualification
from teachers
where fir_degr = '北京大学' or '清华大学' And head_teacher is not null
order by name asc
2025-03-30 条件过滤-符合条件的班主任  
select	name , subject , class_code , qualification
from teachers
where fir_degr = '北京大学' or '清华大学' 
	And head_teacher is not null
order by name asc
2025-03-30 条件过滤-找出所有教授数学且具有高级职称的教师  
select name , subject , class_code , qualification
from teachers
where subject = '数学' and qualification = 'Senior'
2025-03-30 条件过滤-查找2009年出生的女学生  
select student_id , name , birth_date
from	students
where year(birth_date) = 2009 and gender = 'f'
order by birth_date asc
2025-03-30 条件过滤-查找2009年出生的女学生  
select student_id , name , birth_date
from	students
where year(birth_date) = 2009
order by birth_date asc
2025-03-30 优异物理成绩的分布  
select 
	st.class_code, 
	count(distinct st.student_id) as num_students_90_plus,
	round(avg(sc.score) , 2)as avg_score_90_plus,
	te.name as physics_teacher
from 
	students as st 
join 
	scores sc on st.student_id = sc.student_id
join 
	teachers te on te.class_code like concat('%',st.class_code,'%')
where 
	te.subject = '物理' 
and sc.score >= 90 
and sc.subject = '物理'
group by 
	st.class_code, te.name
order by 
	avg_score_90_plus desc
2025-03-30 S1年级物理成绩前10名（2）  
WITH ranked_scores AS (
SELECT 
        s.student_id, 
        s.name, 
        sc.score,
        RANK() OVER (ORDER BY sc.score DESC) AS ranking
    FROM 
        students s
    JOIN 
        scores sc ON s.student_id = sc.student_id
    WHERE 
        s.grade_code = 'S1' 
        AND sc.subject = '物理' 
)
SELECT 
    student_id, 
    name, 
    score, 
    ranking
FROM 
    ranked_scores
WHERE 
    ranking <= 10
ORDER BY 
    ranking;