全站第 43/1231

解决了 32/334 题

中等: 0/75
入门: 21/77
困难: 0/29
简单: 10/114
草履虫: 1/39

过去1年一共提交 67

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

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

达成1题里程碑2025-04-02
达成2题里程碑2025-04-02
达成5题里程碑2025-04-03
达成10题里程碑2025-04-03
达成20题里程碑2025-04-12
完成w12打卡2025-04-23
完成w23打卡2025-05-14
完成w24打卡2025-04-23
完成w25打卡2025-04-23
完成w26打卡2025-04-23
完成w29打卡2025-06-13
完成w30打卡2025-06-13

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
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%'