全站第 50/1262 名

解决了 28/335 题

中等: 0/76
入门: 26/77
困难: 0/29
简单: 1/114
草履虫: 1/39

过去1年一共提交 46 次

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

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

达成1题里程碑2025-05-07
达成2题里程碑2025-05-07
达成5题里程碑2025-05-07
达成10题里程碑2025-05-26
达成20题里程碑2025-05-26
完成w13打卡2025-05-27
完成w33打卡2025-05-26

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-05-28 分组与聚合函数（5）五花八门的项目，其实都有固定套路（2）  
select trx_amt,count(trx_amt) as total_trx_cnt,count(distinct usr_id) as unique_usr_cnt,count(trx_amt) / count(distinct usr_id) as avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
and (
(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))
group by trx_amt
order by avg_trx_per_user desc
limit 5;
2025-05-28 分组与聚合函数（5）五花八门的项目，其实都有固定套路（2）  
select trx_amt,count(trx_amt) as total_trx_cnt,count(distinct usr_id) as unique_usr_cnt,count(trx_amt)/count(distinct usr_id) as avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
and (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)
group by trx_amt
order by avg_trx_per_user desc
limit 5;
2025-05-28 分组与聚合函数（5）五花八门的项目，其实都有固定套路（2）  
select trx_amt,count(trx_amt) as total_trx_cnt,count(distinct usr_id) as unique_usr_cnt,count(trx_amt)/count(distinct usr_id) as avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
and (trx_time between '2023-01-01'and '2024-06-30')
group by trx_amt
order by avg_trx_per_user desc
limit 5;
2025-05-27 CASE WHEN-老中青教师数量  
select CASE 
    WHEN enter_date >= '2010-01-01' THEN '青年教师'
    WHEN enter_date < '2000-01-01' THEN '资深教师'
    ELSE '中年教师'
    END AS '类型',
    COUNT(*) AS 'number'
FROM teachers
GROUP BY 类型
2025-05-27 CASE WHEN-老中青教师数量  
select
	case 
	when enter_date >= '2010-01-01' then '青年教师'
when enter_date < '2000-01-01' then '资深教师'
else '中年教师' end as type,
count(teacher_id) as number
from teachers
group by type
2025-05-27 CASE WHEN-男女学生的数量  
select case when gender = 'm' then '男' when gender = 'f' then '女' else 'none' endas '性别',count(student_id) '数量' from students
group by gender
2025-05-27 一线城市历年平均气温  
select year(dt) as Y
    ,cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as '北京'
    ,cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as 上海
    ,cast(avg(case when city='shenzhen' then tmp_h else null end) as decimal(4,2)) as 深圳
	,cast(avg(case when city='guangzhou' then tmp_h else null end) as decimal(4,2)) as 广州
from weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)
2025-05-27 一线城市历年平均气温  
select year(dt) as Y
    ,cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as '北京'
    ,cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as 上海
 		 ,cast(avg(case when city='guangzhou' then tmp_h else null end) as decimal(4,2)) as 广州
    ,cast(avg(case when city='shenzhen' then tmp_h else null end) as decimal(4,2)) as 深圳
from weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)
2025-05-27 一线城市历年平均气温  
select year(dt) as Y
    ,cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as '北京'
    ,cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as 上海
,cast(avg(case when city='guangzhou' then tmp_h else null end) as decimal(4,2)) as 广州
    ,cast(avg(case when city='shenzhen' then tmp_h else null end) as decimal(4,2)) as 深圳
from weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)
2025-05-27 文科潜力股  
select * from scores
where exam_date = '2024-06-30'
and ((subject = '历史' and score >= 90) or (subject = '地理' and score >= 90) or (subject = '政治' and score >= 90))
order by score desc ,student_id,subject
2025-05-27 给英语成绩中上水平的学生拔尖  
select * from scores
where exam_date = '2024-06-30' and subject = '英语' and score between 100 and 110
order by score desc
2025-05-27 找出三个班级的女生  
select * from students
where class_code in ('C219','C220','C221') and gender = 'f'
 order by student_id
2025-05-27 语文数学英语至少1门超过100分的同学  
select student_id,chinese,math,english from subject_score
where chinese >100 or math >100 or english >100 
 order by chinese asc
2025-05-26 HAVING-语数英优异的学生  
select student_id,sum(score) total_score from scores
where subject in ('语文', '数学', '英语')and exam_date = '2024-06-30'
group by student_id
having sum(score) > 330
2025-05-26 HAVING-执教教师超过3人的科目  
select subject from teachers
group by subject
having count(teacher_id)>=3
2025-05-26 聚合函数-比较两位同学的数学成绩  
select student_id,max(score),min(score),avg(score) from scores
where student_id in (460093,735011) and subject = '数学'
group by student_id
2025-05-26 聚合函数-735011学生的语文成绩  
select max(score),min(score),avg(score) from scores
where subject = '语文' and student_id = '735011'
2025-05-26 聚合函数-735011学生的语文成绩  
select subject,max(score),min(score),avg(score) from scores
where subject = '语文' and student_id = '735011'
2025-05-26 GROUP BY-年龄最大学生的出生日期  
select class_code,min(birth_date) from students
group by class_code
order by class_code asc
2025-05-26 GROUP BY-年龄最大学生的出生日期  
select class_code,max(birth_date) from students
group by class_code
order by class_code asc