排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-26 S1年级物理成绩前10名(1) 
select s.student_id,
s.name,
c.score,
row_number()over() as rnk
from students s
left join scores c
on s.student_id=c.student_id
where s.grade_code="S1"
and c.subject="物理"
order by c.score desc
limit 10
这样符合题目要求吗
啥也没说
2024-12-26 一线城市历年平均气温 
null、0的区别是这样吗,mull不计入计算,而0参与计算,增加了计算平均气温时的分母
bingbo!
2024-12-24 多云天气天数 
select city,
sum(case when con like "%多云%" then 1 else 0 end) as cloudy_days,
concat(round(100*sum(case when con like "%多云%" then 1 else 0 end)/count(1) ,2) ,"%") as p
from weather_rcd_china 
where left(dt,4)=2021
group by city
order by 3 desc
救命,本题的正确写法是什么,怎么用round可以,cast decimal就不行呢
round和cast as decimal是有区别的这个你知道不? round(23.657,2)=23.66, decimal的话等于23.65。
2024-12-18 分组与聚合函数(5)想知道何时成瘾,用Max Or Min? 
order by trx_time
limit 1
最早用这样会不会简单些
思路新奇,给你点赞,这里主要是为了考察min

提交记录

提交日期 题目名称 提交代码
2024-12-27 用户听歌习惯的时间分布 
select user_id,
date_format(start_time,"%W") as day_of_week,
count(if_finished) as listens_per_day
from listen_rcd
group by user_id,date_format(start_time,"%W")
order by user_id,date_format(start_time,"%W")
2024-12-27 用户听歌习惯的时间分布 
select user_id,
date_format(start_time,"%W") as day_of_week,
count(if_finished) as listens_per_day
from listen_rcd
group by user_id,date_format(start_time,"%W")
2024-12-27 特定歌曲的播放记录 
select * 
from listen_rcd
where song_id=13
and date(start_time) between "2023-12-10" and "2023-12-31"
order by start_time
2024-12-27 特定歌曲的播放记录 
select * 
from listen_rcd
where song_id=13
and date(start_time) between "2023-12-10" and "2023-12-31"
2024-12-27 按终点统计行程次数 
select end_loc,count(1) as cnt
from hello_bike_riding_rcd 
group by end_loc
order by 2 desc
2024-12-27 海王发红包 
select *
from tx_red_pkt_rcd
where snd_usr_id in
(
select snd_usr_id
 from tx_red_pkt_rcd
where pkt_amt in(200,520)
group by snd_usr_id
having count(case when pkt_amt in(200,520)then 1end)>=5
 )
 order by snd_usr_id,snd_datetime
2024-12-27 海王发红包 
select *
from tx_red_pkt_rcd
where snd_usr_id in
(
select snd_usr_id
 from tx_red_pkt_rcd
where pkt_amt in(200,520)
group by snd_usr_id
having count(case when pkt_amt in(200,520)then 1end)>=5
 )
 and rcv_datetime>=snd_datetime
 order by snd_usr_id,snd_datetime
2024-12-27 海王发红包 
select *
from tx_red_pkt_rcd
where snd_usr_id in
(
select snd_usr_id
 from tx_red_pkt_rcd
where pkt_amt in(200,520)
group by snd_usr_id
having count(case when pkt_amt in(200,520)then 1end)>=5
 )
 and rcv_datetime>=snd_datetime
2024-12-27 海王发红包 
select *
from tx_red_pkt_rcd
where snd_usr_id in
(
select snd_usr_id
 from tx_red_pkt_rcd
group by snd_usr_id
having (count(snd_usr_id)>=5 and (pkt_amt>=520 or pkt_amt>=200))
 )
 and rcv_datetime>=snd_datetime
2024-12-27 接收红包金额绿茶榜 
select rcv_usr_id,sum(pkt_amt) as sum_trx_amt
from tx_red_pkt_rcd
where rcv_datetime>=snd_datetime
 group by rcv_usr_id
 order by 2 desc
 limit 10
2024-12-27 接收红包金额绿茶榜 
select snd_usr_id,sum(pkt_amt) as sum_trx_amt
from tx_red_pkt_rcd
 group by snd_usr_id
 order by 2 desc
 limit 10
2024-12-27 接收红包金额绿茶榜 
select rcv_usr_id,sum(pkt_amt) as sum_trx_amt
from tx_red_pkt_rcd
 group by rcv_usr_id
 order by 2 desc
 limit 10
2024-12-27 红包金额土豪榜 
select snd_usr_id,sum(pkt_amt) as sum_trx_amt
from tx_red_pkt_rcd
group by snd_usr_id
order by 2 desc
limit 10
2024-12-27 总分超过300分的学生 
select student_id
from subject_score 
where (chinese+math+english)>=300
order by 1
2024-12-27 至少两门科目大于等于110分的学生 
select 
*
from subject_score
where
(
chinese>=110 and math>=110
or
chinese>=110 and english>=110
or
math>=110 and english>=110
)
order by 1
2024-12-27 渣男腰子可真行,端午中秋干不停 
select *
from cmb_usr_trx_rcd 
where usr_id="5201314520"
and (date(trx_time) between "2024-06-08" and "2024-06-10"
or date(trx_time) between "2024-09-15" and "2024-09-17")
order by trx_time
2024-12-27 小结-行转列,展开学生成绩(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;
2024-12-27 小结-行转列,展开学生成绩(1) 
select 
exam_date,
case when subject="语文" then score else null end as "chinese_score",
case when subject="数学" then score else null end as "math_score",
case when subject="英语" then score else null end as "english_score"
from scores
where student_id="460093"
and subject in ("语文","数学","英语")
2024-12-27 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
2024-12-27 HAVING-语数英优异的学生 
select
student_id,
sum(score) as total_score
from scores 
where subject in ("语文","数学","英语")
group by student_id
having sum(score)>330