排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-04-03 条件过滤-找出所有教授数学且具有高级职称的教师  
select name,subject,class_code,qualification
from teachers 
where subject = '数学' and qualification = 'Sensior'
order by name asc;
 为什么这个运不出结果
 
啥也没说
2025-04-02 S1年级物理成绩前10名（1）  
select
    s.student_id,
    s.name,
    sc.score,
    row_number()over(order by sc.score desc) as rnk
from students s
left join scores sc 
    on sc.student_id = s.student_id
where sc.subject = '物理'
    and s.grade_code = 'S1'
order by rnk,s.student_id
limit 10;
这个输出结果都是一样的为什么不正确？
 
编号对不上哦。

提交记录

提交日期 题目名称 提交代码
2025-04-25 分组与聚合函数（5）想知道何时成瘾，用Max Or Min？  
select 
usr_id,
min(trx_time) as first_time,
mch_nm
from cmb_usr_trx_rcd 
where usr_id ='5201314520' and mch_nm = "红玫瑰按摩保健休闲"
group by usr_id;
2025-04-25 分组与聚合函数（5）五花八门的项目，其实都有固定套路（2）  
select 
trx_amt,
count(*) as total_trx_cnt,
count(distinct usr_id) as unique_usr_cnt,
round(count(*)/count(distinct usr_id),4) as avg_trx_per_user
from cmb_usr_trx_rcd 
where ((year(trx_time) = 2023 and month(trx_time) between 1 and 12)
 or (year(trx_time) = 2024 and month(trx_time) between 1 and 6))
and mch_nm = "红玫瑰按摩保健休闲"
group by trx_amt
order by round(count(*)/count(distinct usr_id),4) desc
limit 5;
2025-04-25 分组与聚合函数（5）五花八门的项目，其实都有固定套路（2）  
select 
trx_amt,
count(*) as total_trx_cnt,
count(distinct usr_id) as unique_usr_cnt,
round(count(*)/count(distinct usr_id),4) as avg_trx_per_user
from cmb_usr_trx_rcd 
where year(trx_time) = 2023
and month(trx_time) between 1 and 6
and mch_nm = "红玫瑰按摩保健休闲"
group by trx_amt
order by round(count(*)/count(distinct usr_id),4) desc
limit 5;
2025-04-25 分组与聚合函数（5）五花八门的项目，其实都有固定套路（2）  
select 
trx_amt,
count(*) as total_trx_cnt,
count(distinct usr_id) as unique_usr_cnt,
round(count(*)/count(distinct usr_id),4) as avg_trx_per_user
from cmb_usr_trx_rcd 
where year(trx_time) = 2023
and month(trx_time) between 1 and 6
and mch_nm = "红玫瑰按摩保健休闲"
group by trx_amt
order by count(*) desc
limit 5;
2025-04-25 分组与聚合函数（3）五花八门的项目，其实都有固定套路（1）  
select 
trx_amt,
count(*) as trx_cnt
from cmb_usr_trx_rcd 
where year(trx_time) = 2024 
and month(trx_time) between 1 and 7
and mch_nm = "红玫瑰按摩保健休闲"
group by trx_amt
order by count(*) desc
limit 5 ;
2025-04-25 分组与聚合函数（2）擦边营收怎么样，聚合函数可看出  
select date(trx_time) as trx_date,
max(trx_amt) as max_trx_amt,
min(trx_amt) as min_tex_amt,
round(sum(trx_amt)/count(*),6) as avg_trx_amt,
sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd 
where mch_nm = "红玫瑰按摩保健休闲"
and date(trx_time) between '2024-09-01' and '2024-09-30'
group by date(trx_time)
order by date(trx_time);
2025-04-25 分组与聚合函数（1）Money全都花在哪，GroupBy来查一查  
select mch_nm,sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd 
where usr_id = '5201314520'
and year(trx_time) = 2024
group by mch_nm
order by sum(trx_amt) desc;
2025-04-25 条件过滤（3）Hour函数很给力，组合条件要仔细  
select * 
from cmb_usr_trx_rcd 
where usr_id = '5201314520'
and date(trx_time) between '2024-09-01' and '2024-09-30'
and ((hour(trx_time) >= 22)
or (hour(trx_time) between 0 and 5))
order by trx_time asc;
2025-04-25 条件过滤（3）Hour函数很给力，组合条件要仔细  
select * 
from cmb_usr_trx_rcd 
where usr_id = '5201314520'
and date(trx_time) between '2024-09-01' and '2024-09-30'
and hour(trx_time) >= 22
or hour(trx_time) between 0 and 5
order by trx_time asc;
2025-04-25 条件过滤（3）Hour函数很给力，组合条件要仔细  
select * 
from cmb_usr_trx_rcd 
where usr_id = '5201314520'
and date(trx_time) between '2024-09-01' and '2024-09-30'
and hour(trx_time) >= 22
or hour(trx_time) between 0 and 5
order by trx_time ;
2025-04-25 条件过滤（2）半夜活动有猫腻，Hour函数给给力  
select * 
from cmb_usr_trx_rcd 
where usr_id = '5201314520'
and date(trx_time) between '2024-09-01' and '2024-09-30'
and hour(trx_time) between 1 and 5
order by trx_time asc;
2025-04-25 条件过滤（1）异地男友有异常，数分闺蜜来帮忙  
select usr_id,mch_nm,trx_time,trx_amt
from cmb_usr_trx_rcd 
where usr_id = '5201314520'
and date(trx_time) between '2024-09-01' and '2024-09-30'
group by usr_id,mch_nm,trx_time,trx_amt
order by trx_time asc;
2025-04-25 条件过滤（1）异地男友有异常，数分闺蜜来帮忙  
select usr_id,mch_nm,trx_time,trx_amt
from cmb_usr_trx_rcd 
where usr_id = 5201314520
and trx_time between '2024-09-01' and '2024-09-30'
group by usr_id,mch_nm,trx_time,trx_amt
order by trx_time asc;
2025-04-25 文科潜力股  
select student_id,subject,score,exam_date
from scores 
where exam_date = '2024-06-30'
and case when subject in ("历史","政治","地理") then score end >= 90
group by student_id,subject,score,exam_date
order by score desc,student_id,subject;
2025-04-25 给英语成绩中上水平的学生拔尖  
select student_id,subject,score,exam_date
from scores 
where exam_date = '2024-06-30'
and case when subject = "英语" then score end between 100 and 110
group by student_id,subject,score,exam_date
order by score desc;
2025-04-25 找出三个班级的女生  
select student_id,name,class_code,grade_code,birth_date,residence,gender
from students 
where class_code in ('C219','C220','C221') and gender = 'f'
group by student_id,name,class_code,grade_code,birth_date,residence,gender
order by student_id;
2025-04-25 语文数学英语至少1门超过100分的同学  
select student_id,chinese,math,english
from subject_score 
where chinese > 100 or math > 100 or english > 100
group by student_id,chinese,math,english
order by chinese asc;
2025-04-25 小结-行转列，展开学生成绩（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 and subject in ("语文","数学","英语")
group by exam_date
order by exam_date;
2025-04-25 HAVING-语数英优异的学生  
select student_id,sum(score) as total_score
from scores 
where subject in ("语文","数学","英语")
and exam_date = "2024-06-30"
group by student_id
having sum(score) > 330;
2025-04-25 HAVING-语数英优异的学生  
select student_id,sum(score) as total_score
from scores 
where subject in ("语文","数学","英语")
and exam_date = "2024-06-30"
group by student_id
having sum(score) >= 330;