全站第 17/815 名

解决了 43/327 题

中等: 1/75
入门: 8/74
困难: 0/28
简单: 0/114
草履虫: 34/36

过去1年一共提交 90 次

Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar

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

达成1题里程碑2025-02-11
达成2题里程碑2025-02-11
达成5题里程碑2025-02-11
达成10题里程碑2025-02-12
达成20题里程碑2025-02-13
完成w1打卡2025-02-11
完成w2打卡2025-02-12
完成w3打卡2025-02-13
完成w4打卡2025-02-13
完成w5打卡2025-02-13
完成w6打卡2025-02-13
完成w7打卡2025-02-16

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
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;