排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2026-01-20 基础标量子查询-带分组  
select usr_id, sum(trx_amt) as total_amt,
	(select avg(trx_amt) from cmb_usr_trx_rcd) as platform_avg_amt
from cmb_usr_trx_rcd
group by usr_id
2026-01-20 按歌手名字字符长度统计歌手个数  
select length(singer_name), count(*)
from singer_info
group by 1
2026-01-20 统计字符长度  
select singer_name, char_length(singer_name) as len
from singer_info
2026-01-20 歌手名字大写  
select upper(singer_name) as uppered_name
from singer_info
2026-01-20 北京有雪的日子  
select dt, tmp_h, tmp_l, con
from weather_rcd_china
where city = 'beijing' and con like '%雪%'
2026-01-20 人数最多的学生姓氏  
select left(name, 1) as surname, count(*) as cnt 
from students
group by surname
order by cnt desc
limit 5
2026-01-16 多云天气天数  
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(con)*100 as decimal(10,2)), '%') as p
from weather_rcd_china
where year(dt) = '2021'
group by city
order by 3 desc
2026-01-16 多云天气天数  
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(10,2)),'%')  as p
from 
    weather_rcd_china
where 
    year(dt)=2021
group by 
    city
order by 
    3 desc
2026-01-16 多云天气天数  
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(10,2)), '%') as p
from weather_rcd_china
where year(dt) = '2011'
group by city
order by p desc
2026-01-16 多云天气天数  
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(con) * 100 as decimal(10,2)), '%') as p
from weather_rcd_china
where year(dt) = '2011'
group by city
order by p desc
2026-01-16 多云天气天数  
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(con) * 100 as decimal(4,2)), '%') as p
from weather_rcd_china
where year(dt) = '2011'
group by city
order by p desc
2026-01-16 多云天气天数  
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(con) * 100 as decimal(4,2)), '%') as p
from weather_rcd_china
where year(dt) = '2011'
group by city
order by p desc
2026-01-16 德州扑克起手牌- 手对  
select *
from hand_permutations
where left(card1, 1) = left(card2, 1)
2026-01-16 德州扑克起手牌- A花  
select * 
from hand_permutations
where 
	right(card1, 1) = right(card2, 1)
	and
(card1 like 'A%' or card2 like 'A%')
order by id
2026-01-16 德州扑克起手牌- A花  
select * 
from hand_permutations
where concat(card1, card2) rlike 'A*'
order by id
2026-01-16 德州扑克起手牌- A花  
select * 
from hand_permutations
where concat(card1, card2) rlike '^A'
order by id
2026-01-16 德州扑克起手牌- A花  
select * 
from hand_permutations
where concat(card1, card2) like 'A%'
order by id
2026-01-16 德州扑克起手牌-最强起手牌KK+  
select id, card1, card2
from hand_permutations
where concat(card1,card2) rlike 'A.A.|\A.K.|\K.K.|\K.A.'
order by id
2026-01-16 字符串与通配符（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
where mch_nm like '按摩保健休闲%' or	lower(mch_nm) rlike '按摩|\保健|\休闲|\养生|\SPA|\会所'
group by reg_rules
2026-01-16 字符串与通配符（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
where mch_nm like '%按摩保健休闲%' or	lower(mch_nm) rlike '按摩|\保健|\休闲|\养生|\SPA|\会所'
group by reg_rules