排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2026-03-03 HAVING子句中的子查询  
select mch_nm, sum(trx_amt) as mch_total_amt
from cmb_usr_trx_rcd
group by mch_nm
having mch_total_amt > (select avg(trx_amt)*10 from cmb_usr_trx_rcd)
2026-03-03 NOT IN子查询  
select * 
from cmb_usr_trx_rcd 
where usr_id not in
	(select distinct usr_id
from cmb_usr_trx_rcd
where mch_nm = '肯德基')
2026-03-03 IN子查询  
select *
from cmb_usr_trx_rcd
where usr_id in (
	select distinct usr_id
	from cmb_usr_trx_rcd
	where mch_nm = '肯德基'
)
order by usr_id, trx_time
2026-03-03 IN子查询  
select *
from cmb_usr_trx_rcd
where mch_nm = '肯德基'
order by usr_id, trx_time
2026-03-03 IN子查询  
select *
from cmb_usr_trx_rcd
where mch_nm = '肯德基'
2026-03-03 WHERE子查询 + 比较运算符  
select *
from cmb_usr_trx_rcd
where trx_amt > (select avg(trx_amt) from cmb_usr_trx_rcd)
2026-03-03 基础标量子查询-不带分组  
select *,
	(select avg(trx_amt) from cmb_usr_trx_rcd)
from cmb_usr_trx_rcd
where usr_id = '5201314520'
2026-03-03 基础标量子查询-带分组  
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
order by total_amt desc
2026-03-03 人数最多的学生姓氏  
select left(name, 1) as surname, count(*) as cnt
from students
group by left(name, 1)
order by cnt desc
limit 5
2026-03-03 人数最多的学生姓氏  
select left(name, 1) as surname, count(*) as cnt
from students
group by left(name, 1)
order by cnt desc
2026-03-03 德州扑克起手牌- A花  
select *
from hand_permutations
where 
	right(card1, 1) = right(card2, 1)
and
	(card1 like 'A%' or card2 like 'A%')
order by id;
2026-03-03 德州扑克起手牌- A花  
select *
from hand_permutations
where 
	right(card1, 1) = right(card2, 1)
and
	card1 like 'A%' or card2 like 'A%'
order by id;
2026-03-03 字符串与通配符（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
order by mch_cnt desc;
2026-03-03 字符串与通配符（2）好多关键词做规则，可以使用rlike  
select 
	case 
	when lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*' then '按摩、保健、休闲、养生、SPA、会所'
	when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
	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-03-02 字符串与通配符（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
order by mch_cnt desc
2026-03-02 字符串与通配符（1）名称里面有特服，可以使用通配符  
select count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
2026-02-28 文科潜力股  
select * 
from scores 
where
	(
(subject = '历史' and score >= 90)
or (subject = '地理' and score >= 90)
or (subject = '政治' and score >= 90)
)
and exam_date = '2024-06-30'
order by score desc, student_id, subject
2026-02-28 总分超过300分的学生  
select student_id
from subject_score
where chinese+english+math >= 300
2026-02-28 国庆假期后第一天涨幅高于1%的股票  
select
	ts_code,
open_price,
close_price
from
	daily_stock_prices
where
	trade_date = '2023-10-09' and pct_change > 1
2026-02-27 德州扑克起手牌-最强起手牌KK+  
select *
from hand_permutations
where
 	concat(card1, card2) like '%A%A%' or 
 	concat(card1, card2) like '%A%K%' or
	concat(card1, card2) like '%K%K%' or
	concat(card1, card2) like '%K%A%'
order by id