排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2026-03-26 S1年级物理成绩前10名（2）  
WITH ranked_scores AS (
    SELECT 
        s.student_id, 
        s.name, 
        sc.score,
        rank() OVER ( 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
2026-03-26 S1年级物理成绩前10名（2）  
WITH ranked_scores AS (
    SELECT 
        s.student_id, 
        s.name, 
        sc.score,
        rank() 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
2026-03-24 数学成绩分段统计（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
 inner jOIN 
    scores sc ON s.student_id = sc.student_id
WHERE 
    sc.subject = '数学' 
    AND sc.exam_date = '2024-06-30'
GROUP BY 
    score_range
2026-03-24 S1年级物理成绩前10名（2）  
WITH ranked_scores AS (
    SELECT 
        s.student_id, 
        s.name, 
        sc.score,
        dense_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 = '物理'
)
SELECT 
    student_id, 
    name, 
    score, 
    ranking
FROM 
    ranked_scores
WHERE 
    ranking <= 10
ORDER BY 
    ranking;
2026-03-19 查询所有终点是餐饮类地点的行程记录  
select r.* from didi_sht_rcd r joinloc_nm_ctgl on r.end_loc=l.loc_nm
where l.loc_ctg ='餐饮'
order by start_tm
2026-03-19 查询所有终点是餐饮类地点的行程记录  
select * from didi_sht_rcd r joinloc_nm_ctgl on r.end_loc=l.loc_nm
where l.loc_ctg ='餐饮'
order by start_tm
2026-03-19 查询所有终点是餐饮类地点的行程记录  
select * from didi_sht_rcd r joinloc_nm_ctgl on r.end_loc=l.loc_nm
where loc_ctg ='餐饮'
order by start_tm
2026-03-18 小结-行转列，展开学生成绩（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
 group by 1
2026-03-18 通勤、午休、临睡个时间段活跃人数分布  
WITH 
distinct_login_days AS (
    SELECT 
      usr_id,
       login_time
    FROM 
        user_login_log
 WHERE 
        login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01')
)
SELECT 
   COUNT(DISTINCT CASE
        WHEN TIME(login_time) BETWEEN '07:30:00' AND '09:30:00'
             OR TIME(login_time) BETWEEN '18:30:00' AND '20:30:00' THEN usr_id
    END) AS commute,
    COUNT(DISTINCT CASE
        WHEN TIME(login_time) BETWEEN '11:30:00' AND '14:00:00' THEN usr_id
    END) AS lunch_break,
    COUNT(DISTINCT CASE
        WHEN TIME(login_time) BETWEEN '22:30:00' AND '23:59:59' THEN usr_id
        WHEN TIME(login_time) BETWEEN '00:00:00' AND '01:00:00' THEN usr_id
    END) AS bedtime
FROM 
    distinct_login_days;
2026-02-27 多云天气天数  
select city,
    sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days
    ,concat(cast(sum(case when con like '%多云%' then 1 else 0 end)/count(1)*100 as decimal(4,2)),'%')  as p
from 
    weather_rcd_china
where 
    year(dt)=2021
group by 
    city
order by 
    3 desc
2026-02-04 绘制小时进入人数曲线  
select hour(enter_time)hour_entered,count(usr_id)enter_count	 from ks_live_t1
group by 1
order by 1 asc
2026-01-29 条件过滤（1）异地男友有异常，数分闺蜜来帮忙  
select
    *
from
    cmb_usr_trx_rcd
where 
    date(trx_time) between '2024-09-01' and '2024-09-30'
    and usr_id='5201314520'
order by 
    trx_time
2026-01-27 表连接（2）渣男去过我对象没去过，那就用LeftJoin  
select a.mch_nm as asshole_tried,a.trx_cnt, b.mch_nm as darling_tried from 
(select mch_nm, count(1) trx_cnt
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5201314520'
group by mch_nm
having count(1) >=20)a
left join
(select distinct mch_nm
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5211314521')b
on a.mch_nm = b.mch_nm order by 2 desc
2026-01-27 表连接（2）渣男去过我对象没去过，那就用LeftJoin  
select a.mch_nm as asshole_tried,a.trx_cnt, b.mch_nm as darling_tried from 
(select mch_nm, count(trx_time) trx_cnt
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5201314520'
group by mch_nm
having count(1) >=20)a
left join
(select distinct mch_nm
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5211314521')b
on a.mch_nm = b.mch_nm order by 2 desc
2026-01-27 表连接（2）渣男去过我对象没去过，那就用LeftJoin  
select a.mch_nm as asshole_tried,a.trx_cnt, b.mch_nm as darling_tried from 
(select mch_nm, count(trx_amt) trx_cnt
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5201314520'
group by mch_nm
having count(1) >=20)a
left join
(select distinct mch_nm
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5211314521')b
on a.mch_nm = b.mch_nm order by 2 desc
2026-01-26 字符串与通配符（2）好多关键词做规则，可以使用rlike  
select
    case 
        when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
        when lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*' then '按摩、保健、休闲、养生、SPA、会所'
else ' '
    end as reg_rules,
    count(distinct mch_nm) as mch_cnt
from
    cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
   or lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by reg_rules
order by mch_cnt desc;
2026-01-26 字符串与通配符（2）好多关键词做规则，可以使用rlike  
select
    case 
        when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
        when lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*' then '按摩、保健、休闲、养生、SPA、会所'
    end as reg_rules,
    count(distinct mch_nm) as mch_cnt
from
    cmb_usr_trx_rcd
group by reg_rules
order by mch_cnt desc;
2026-01-23 分组与聚合函数（6）想知道渣男有多坏，疯狂使用GroupBy  
select usr_id,mch_nm,sum(trx_amt)trx_amt,count(1)trx_cnt, min(trx_time)first_time from cmb_usr_trx_rcd 
where 
    usr_id='5201314520'
    and trx_amt>=288
group by usr_id,mch_nm
order by trx_cnt desc
2026-01-23 分组与聚合函数（6）想知道渣男有多坏，疯狂使用GroupBy  
select usr_id,mch_nm,sum(trx_amt)trx_amt,count(1)trx_cnt, min(trx_time)first_time from cmb_usr_trx_rcd 
where 
    usr_id='5201314520'
    and trx_amt>=288
group by usr_id,mch_nm
order by trx_cnt desc
limit 5
2026-01-23 分组与聚合函数（6）想知道渣男有多坏，疯狂使用GroupBy  
select usr_id,mch_nm,sum(trx_amt)trx_amt,count(trx_amt)trx_cnt, min(trx_time)first_time from cmb_usr_trx_rcd 
group by usr_id,mch_nm
order by trx_cnt desc
limit 5