排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-02-16 S1年级物理成绩前10名（1）  
ROW_NUMBER() 在分数相同时的默认排序行为不同
 
啥也没说

提交记录

提交日期 题目名称 提交代码
2025-02-17 条件过滤-查找1994年至1997年毕业的女教师  
select
	name,
subject,
class_code,
graduate_date
from 
	teachers
where
	gender = 'f' and (year(graduate_date) between 1994 and 1997)
order by 
	graduate_date asc;
2025-02-17 条件过滤-符合条件的班主任  
select 
	name,
subject,
class_code,
qualification
from
	teachers
where
	(fir_degr = '北京大学' or fir_degr = '清华大学') and head_teacher is not null
order by
	name;
2025-02-17 条件过滤-符合条件的班主任  
select 
	name,
subject,
class_code,
qualification
from
	teachers
where
	fir_degr = '北京大学' or fir_degr = '清华大学'
order by
	name;
2025-02-17 条件过滤-符合条件的班主任  
select * from teachers limit 5;
2025-02-17 条件过滤-找出所有教授数学且具有高级职称的教师  
select name,subject,class_code,qualification
from
	teachers
where qualification = 'Senior' and subject = '数学'
order by name;
2025-02-17 条件过滤-找出所有教授数学且具有高级职称的教师  
select name,subject,qualification
from
	teachers
where qualification = 'Senior' and subject = '数学'
order by name;
2025-02-17 条件过滤-找出所有教授数学且具有高级职称的教师  
select name,subject,qualification
from
	teachers
where qualification = 'Senior'
order by name;
2025-02-17 条件过滤-查找2009年出生的女学生  
select student_id,name,birth_date from students where year(birth_date) = 2009 and gender = 'f' order by birth_date;
2025-02-17 条件过滤-查找2009年出生的女学生  
select student_id,name,birth_date from students where year(birth_date) = 2009 and gender = 'f' order by birth_date limit 5;
2025-02-17 条件过滤-查找2009年出生的女学生  
select * from students where year(birth_date) = 2009 and gender = 'f' order by birth_date limit 5;
2025-02-17 条件过滤-查找2009年出生的女学生  
select * from students where year(birth_date) = 2009 and gender = 'f' order by birth_date;
2025-02-17 数学成绩分段统计（1）  
SELECT 
    CASE 
        WHEN sc.score >= 110 THEN '[110, 120]'
        WHEN sc.score >= 90 THEN '[90, 110)'
        WHEN sc.score >= 60 THEN '[60, 90)'
        ELSE '[0, 60)'
    END AS score_range,
    COUNT(*) AS num_students
FROM 
    students s
JOIN 
    scores sc ON s.student_id = sc.student_id
WHERE 
    sc.subject = '数学' 
    AND sc.exam_date = '2024-06-30'
GROUP BY 
    score_range
ORDER BY 
    score_range DESC;
2025-02-17 数学成绩分段统计（1）  
select
	case 
	when sc.score >= 110 then '[110,120]'
when sc.score >= 90 then '[90,110)'
when sc.score >= 60 then '[60,90)'
	else '[0,60)'
end as score_range,
count(*) as num_students
from 
	students s
join 
	scores sc on s.student_id = sc.student_id
where 
	sc.subject = '数学' 
    AND sc.exam_date = '2024-06-30'
group by
	score_range
order by
	score_range desc;
2025-02-17 数学成绩分段统计（1）  
select
	case 
	when sc.score >= 110 then '[110,120]'
when sc.score >= 90 then '[90,110)'
when sc.score >= 60 then '[60,90)'
	else '[0,60)'
end as score_range,
count(*) as num_students
from 
	students s
join 
	scores sc on s.student_id = sc.student_id
where 
	 sc.subject = '数学' 
    AND sc.exam_date = '2024-06-30'
group by
	score_range
order by
	score_range desc;
2025-02-17 数学成绩分段统计（1）  
select
	case 
	when sc.score >= 110 then '[110,120]'
when sc.score >= 90 then '[90,110)'
when sc.score >= 60 then '[60,90)'
	else '[0,60)'
end as score_range,
count(*) as num_students
from 
	students s
join 
	scores sc on s.student_id = sc.student_id
where 
	sc.exam_date = '2024-6-30'
and sc.subject = '数学'	
group by
	score_range
order by
	score_range desc;
2025-02-17 数学成绩分段统计（1）  
select
	case 
	when sc.score >= 110 then '[110,120]'
when sc.score >= 90 then '[90,110)'
when sc.score >= 60 then '[60,90)'
	else '[0,60)'
end as score_range,
count(*) as num_students
from 
	students s
join 
	scores sc on s.student_id = sc.student_id
where 
	sc.exam_date = '2024-6-30'
and
sc.subject = '数学'	
group by
	score_range
order by
	score_range desc;
2025-02-16 S1年级物理成绩前10名（2）  
SELECT
student_id,
name,
score,
ranking
FROM (
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 = '物理'
) AS ranked_scores
WHERE
ranking < 10;
2025-02-16 S1年级物理成绩前10名（2）  
SELECT
student_id,
name,
score,
ranking
FROM (
SELECT
s.student_id,
s.name,
sc.score,
RANK() OVER (PARTITION BY sc.subject 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 = '物理'
) AS ranked_scores
WHERE
ranking < 10;
2025-02-16 S1年级物理成绩前10名（1）  
SELECT
*,
ROW_NUMBER() OVER (ORDER BY sc.score DESC, st.name desc) AS rnk
FROM
students AS st
INNER JOIN scores AS sc
ON st.student_id = sc.student_id
WHERE
st.grade_code = 'S1' and sc.score = 100
ORDER BY 
rnk
LIMIT 30;
2025-02-16 S1年级物理成绩前10名（1）  
select
	s.student_id,
s.name,
sc.score,
row_number()over(partition by sc.subject order by score desc)
from 
	students s 
join 
 	scores sc on s.student_id = sc.student_id
where
	sc.subject = '物理' and s.grade_code = 'S1'
limit 10;