排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-05-14 绘制小时进入人数曲线  
select
	lpad(hour(enter_time),2,'0') as hour_entered,
    count(1) as enter_count
from
	ks_live_t1 t1 join ks_live_t2 t2 on t1.live_id = t2.live_id
group by
	hour_entered
order by
	hour_entered asc
菜鸟啥也不懂，是不是可以不用子查询
 
nice。这哪里是菜鸟，明明是换装大师。点赞。
2025-04-03 分组与聚合函数（2）擦边营收怎么样，聚合函数可看出  
整了半天 这题只看商户不看用户
 
擦边营收啊哈哈哈，营收就是特指店家维度
2025-04-03 条件过滤（3）Hour函数很给力，组合条件要仔细  
讲道理这里能用union 么，我用了之后运行出错，最后还是参照答案写
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) <= '5'
    )
order by
	trx_time
 
那必须能union，你想怎么union，union 和or是天生一对啊
2025-04-03 S1年级物理成绩前10名（2）  
先查出前十名发现不对，看答案实际是取小于10的，最后使用子查询搞定，参考答案的定义方法不会用
select 
	t.student_id, 
    t.name, 
    t.score,
    t.ranking
from(
	select 
        s1.student_id, 
        s1.name, 
        s2.score, 
        (rank() over(order by s2.score desc)) as ranking
	from 
        students s1
        join scores s2 on s1.student_id = s2.student_id
	where 
        s1.grade_code = 'S1' and subject = '物理'
) t 
where 
	t.ranking <= 10
order by
	ranking;
 
这题坑很多。我应该再多出2题的。这样就能把四种情况都给大家搞清楚。
1.order by score limit 10，不带窗口函数
2\3\4、三种排序窗口函数。
你先自己试试吧

提交记录

提交日期 题目名称 提交代码
2025-06-13 冬季下雪天数  
select
city,
sum(case when con like '%雪%' then 1 else 0 end) as snowy_days
from
	weather_rcd_china
where
	month(dt) in (12,1,2)
group by
	city
order by
	2 desc
2025-06-13 多云天气天数  
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(*)*100 as decimal(10,2)),'%') as p
from
	weather_rcd_china
where
	year(dt) = 2021
group by
	city
order by 
    3 desc
2025-06-13 城市平均最高气温  
select
	city,
cast(avg(tmp_h) as decimal(4,2)) as avg_tmp_h
from
	weather_rcd_china
where	
	year(dt) = 2021
group by
	city
order by
	avg_tmp_h desc
2025-06-13 城市平均最高气温  
select
	city,
cast(avg(tmp_h) as decimal(4,2)) as avg_tmp_h
from
	weather_rcd_china
group by
	city
order by
	avg_tmp_h desc
2025-06-13 城市平均最高气温  
select
	city,
cast(avg(tmp_h) as decimal(4,2))
from
	weather_rcd_china
group by
	city
order by
	avg(tmp_h) desc
2025-06-13 城市平均最高气温  
select
	city,
avg(tmp_h)
from
	weather_rcd_china
group by
	city
order by
	avg(tmp_h) desc
2025-05-27 用户"kjhd30"的第一笔未完成订单  
select 
	* 
from 
	didi_order_rcd 
where
	cust_uid = 'kjhd30'
order by
	order_id
limit	1
2025-05-27 滴滴面试真题（2）打车订单呼叫应答时间  
select
	sum(timestampdiff(second,call_time,grab_time))/count(1)
from
	didi_order_rcd
where
	not grab_time = '1970-01-01 00:00:00'
2025-05-14 不分类别的最火直播间  
select
	t2.live_id,
t2.live_nm,
count(t1.usr_id) as enter_count
from
	ks_live_t1 t1 
join ks_live_t2 t2 
on t1.live_id = t2.live_id
where
	date_format(t1.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
group by
	t1.live_id, t2.live_nm
order by
	enter_count desc
limit 5;
2025-05-14 绘制小时进入人数曲线  
select
	lpad(hour(enter_time),2,'0') as hour_entered,
count(1) as enter_count
from
	ks_live_t1 t1 join ks_live_t2 t2 on t1.live_id = t2.live_id
group by
	hour_entered
order by
	hour_entered
2025-05-14 绘制小时进入人数曲线  
select
	hour(enter_time) as hour_entered,
count(1) as enter_count
from
	ks_live_t1 t1 join ks_live_t2 t2 on t1.live_id = t2.live_id
group by
	hour_entered
order by
	hour_entered
2025-05-14 绘制小时进入人数曲线  
select 
	hour(enter_time) as hour_entered,
	count(1) as enter_count
from 
	ks_live_t1 t1 join ks_live_t2 t2 on t1.live_id = t2.live_id
group by 
	hour(enter_time)
order by 
	hour_entered
2025-04-23 德州扑克起手牌-同花  
select 
	sum(case when right(card1,1) = right(card2,1) then 1 else 0 end)/2 as cnt,
count(1)/2 as ttl_cnt,
cast(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/count(1) AS DECIMAL(4,3)) as p
from hand_permutations
2025-04-23 德州扑克起手牌-同花  
select 
	sum(case when left(card1,1) = left(card2,1) then 1 else 0 end)/2 as cnt,
count(1)/2 as ttl_cnt,
cast(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/count(1) AS DECIMAL(4,3)) as p
from hand_permutations
2025-04-23 德州扑克起手牌-同花  
select 
	sum(case when left(card1,1) = left(card2,1) then 1 else 0 end)/2 as cnt,
count(1)/2 as ttl_cnt,
cast(sum(case when left(card1,1) = left(card2,1) then 1 else 0 end)/count(1) as decimal(4,3)) as p
from hand_permutations
2025-04-23 德州扑克起手牌-同花  
select 
	sum(case when left(card1,1) = left(card2,1) then 1 else 0 end)/2 as cnt,
count(1)/2 as ttl_cnt,
(sum(case when left(card1,1) = left(card2,1) then 1 else 0 end)/2)/(count(1)/2) as p
from hand_permutations
2025-04-23 德州扑克起手牌- 手对  
select * from hand_permutations 
where
	left(card1,1) = left(card2,1)
2025-04-23 德州扑克起手牌- A花  
select 
	* 
from
	hand_permutations 
where
	right(card1,1) = right(card2,1)
and
(card1 like 'A%' or card2 like 'A%')
order by
	id
2025-04-23 德州扑克起手牌- A花  
select 
	* 
from
	hand_permutations 
where
	right(card1,1) = right(card2,1)
and
card1 like 'A%' or card2 like 'A%'
order by
	id
2025-04-23 德州扑克起手牌- A花  
select 
	* 
from
	hand_permutations 
where
	right(card1,1) = right(card2,1)
and
card1 like 'A%' or card2 like 'A%'