排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2026-02-25 一线城市历年平均气温  
select DATE_FORMAT(dt, '%Y'),
avg(case when city='beijing' then tmp_h else null end),
avg(case when city='shanghai' then tmp_h else null end),
avg(case when city='shenzhen' then tmp_h else null end),
avg(case when city='guangzhou' then tmp_h else null end)
from weather_rcd_china 
where year(dt) between 2011 and 2022
group by DATE_FORMAT(dt, '%Y')
2026-02-25 一线城市历年平均气温  
select DATE_FORMAT(dt, '%Y'),
avg(case when city='beijing' then tmp_h else null end),
avg(case when city='shanghai' then tmp_h else null end),
avg(case when city='shenzhen' then tmp_h else null end),
avg(case when city='guangzhou' then tmp_h else null end)
from weather_rcd_china 
group by DATE_FORMAT(dt, '%Y')
2026-02-25 分组与聚合函数（2）擦边营收怎么样，聚合函数可看出  
select DATE_FORMAT(trx_time, '%Y-%m-%d'),max(trx_amt),min(trx_amt),avg(trx_amt),sum(trx_amt) from cmb_usr_trx_rcd 
where mch_nm='红玫瑰按摩保健休闲' and DATE_FORMAT(trx_time, '%Y-%m')='2024-09'
group by DATE_FORMAT(trx_time, '%Y-%m-%d')
2026-02-25 分组与聚合函数（1）Money全都花在哪，GroupBy来查一查  
select mch_nm,sum(trx_amt) sum_trx_amt from cmb_usr_trx_rcd 
where usr_id=5201314520 and DATE_FORMAT(trx_time, '%Y')=2024
group by mch_nm
2026-02-25 条件过滤（2）半夜活动有猫腻，Hour函数给给力  
select * from cmb_usr_trx_rcd where DATE_FORMAT(trx_time, '%Y-%m-%d') between '2024-09-01' and '2024-09-30' and 
HOUR(trx_time) between 1 and 5 
and usr_id=5201314520
2026-02-25 条件过滤（2）半夜活动有猫腻，Hour函数给给力  
select * from cmb_usr_trx_rcd where DATE_FORMAT(trx_time, '%Y-%m-%d') between '2024-09-01' and '2024-09-30' and 
HOUR(trx_time) between 1 and 5 
union all
select * from cmb_usr_trx_rcd where DATE_FORMAT(trx_time, '%Y-%m-%d') between '2024-09-01' and '2024-09-30' and 
DATE_FORMAT(trx_time, '%H:%i:%s')='06:00:00'
2026-02-25 条件过滤（2）半夜活动有猫腻，Hour函数给给力  
select * from cmb_usr_trx_rcd where DATE_FORMAT(trx_time, '%Y-%m-%d') between '2024-09-01' and '2024-09-30' and HOUR(trx_time) between 1 and 5
2026-02-25 条件过滤（2）半夜活动有猫腻，Hour函数给给力  
select * from cmb_usr_trx_rcd where DATE_FORMAT(trx_time, '%Y-%m-%d') between '2024-09-01' and '2024-09-30' and HOUR(trx_time) between 1 and 6
2026-02-25 条件过滤（1）异地男友有异常，数分闺蜜来帮忙  
select * from cmb_usr_trx_rcd where usr_id=5201314520 and DATE_FORMAT(trx_time, '%Y-%m-%d') between '2024-09-01' and '2024-09-30'