排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2025-04-06 不分类别的最火直播间  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-04-06 不分类别的最火直播间  
找出2021年9月12日23点,是时间段还是时间点
 
给你点赞，严谨大师！不过如果按照你的逻辑推论，理论上不存在月活、日活了，所有的表述都应该具体到秒甚至毫秒了。

提交记录

提交日期 题目名称 提交代码
2025-04-06 不分类别的最火直播间  
select a.live_id,live_nm
,count(*)
from ks_live_t1 a
left join ks_live_t2 b
on a.live_id = b.live_id
where substr(enter_time,1,13) = '2021-09-12 23'
group by a.live_id,live_nm
order by count(*) desc limit 5 ;
2025-04-06 不分类别的最火直播间  
select a.live_id,live_nm
,count(*)
from ks_live_t1 a
left join ks_live_t2 b
on a.live_id = b.live_id
where enter_time<= '2021-09-12 23:00:00'
and leave_time>='2021-09-12 23:00:00'
group by a.live_id,live_nm
order by count(*) desc limit 5 ;
2025-04-05 S1年级物理成绩前10名（1）  
WITH ranked_scores AS (
    SELECT 
        s.student_id, 
        s.name, 
        sc.score,
        ROW_NUMBER() OVER (PARTITION BY s.grade_code ORDER BY sc.score DESC) AS rnk
    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, 
    rnk
FROM 
    ranked_scores
WHERE 
    rnk <= 10
ORDER BY 
    rnk,student_id
2025-04-05 S1年级物理成绩前10名（1）  
select
	st.student_id
,st.name
,sc.score
,row_number()over(order by sc.score 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.subject='物理'
ORDER BY 
rnk,st.student_id
limit
	10
2025-04-05 S1年级物理成绩前10名（1）  
select * from (
select 
a.student_id,
name,score
,rank()over( order by score desc) rn 
from students a
left join scores b
on a.student_id=b.student_id
where subject = '物理' 
and grade_code ='S1'
 )a1 where rn <= 10
 ORDER BY 
    rn,student_id
;
2025-04-05 S1年级物理成绩前10名（1）  
select * from (
select 
a.student_id,
name,score
,row_number()over( order by score desc) rn 
from students a
left join scores b
on a.student_id=b.student_id
where subject = '物理' 
and grade_code ='S1'
 )a1 where rn <= 10
 ORDER BY 
    rn,student_id
;
2025-04-05 S1年级物理成绩前10名（1）  
select * from (
select 
a.student_id,
name,score
,row_number()over( order by score desc) rn 
from students a
left join scores b
on a.student_id=b.student_id
where subject = '物理' 
and grade_code ='S1'
 )a1 where rn <= 10
;
2025-04-05 S1年级物理成绩前10名（1）  
select 
a.student_id,
name,score
from students a
left join scores b
on a.student_id=b.student_id
where subject = '物理' 
and grade_code ='S1'
order by score desc limit 10
;
2025-04-05 S1年级物理成绩前10名（1）  
select 
a.student_id,
name,score
from students a
left join scores b
on a.student_id=b.student_id
where subject = '物理' 
order by score desc limit 10
;
2025-04-05 人数最多的学生姓氏  
select
substr(name,1,1),COUNT(*) 
from students 
group by substr(name,1,1 ) 
order by count(*) desc 
limit 5;
2025-04-05 人数最多的学生姓氏  
select
substr(name,1,1)
from students 
group by substr(name,1,1 ) 
order by count(*) desc 
limit 5;
2025-04-05 学生信息和班主任姓名  
select 
a.name,
a.class_code,grade_code,
b.name
from students a
left join teachers b
on a.class_code=b.head_teacher
order by student_id asc
2025-04-05 学生信息和班主任姓名  
select 
a.name,a.class_code,
b.name
from students a
left join teachers b
on a.class_code=b.head_teacher
order by student_id asc