排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2024-12-30 条件过滤(1)异地男友有异常,数分闺蜜来帮忙  已解决

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-01-06 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select
	a.mch_nm as asshole_tried,
a.trx_cnt as trx_cnt,
b.mch_nm as darling_tried
from 
	(select
		mch_nm,
		count(1) as trx_cnt
	from 
		cmb_usr_trx_rcd
	where
		year(trx_time) between 2023 and 2024
		and usr_id = '5201314520'
	group by
		mch_nm
	having count(1) >= 20
	order by trx_cnt desc)
	a left join(
	select
			distinct mch_nm	
		from 
			cmb_usr_trx_rcd
		where
			year(trx_time) between 2023 and 2024
			and usr_id = '5201314521'
		group by
			mch_nm
) b on a.mch_nm = b.mch_nm
group by
	a.mch_nm
order by
	a.trx_cnt desc
2025-01-06 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select
	a.mch_nm as asshole_tried,
a.trx_cnt as trx_cnt,
b.mch_nm as darling_tried
from (
	(select
		mch_nm,
		count(1) as trx_cnt
	from 
		cmb_usr_trx_rcd
	where
		year(trx_time) between 2023 and 2024
		and usr_id = '5201314520'
	group by
		mch_nm
	having count(1) >= 20
	order by trx_cnt desc)
	a left join(
	select
			mch_nm	
		from 
			cmb_usr_trx_rcd
		where
			year(trx_time) between 2023 and 2024
			and usr_id = '5201314521'
		group by
			mch_nm
) b on a.mch_nm = b.mch_nm
)
group by
	a.mch_nm
order by
	a.trx_cnt desc
2025-01-02 表连接(1)你们难道都去过?那就试试用InnerJoin 
select 
	distinct b.mch_nm
from
(select
	distinct mch_nm
from
	cmb_usr_trx_rcd
where 
	usr_id = '5201314520'
and year(trx_time) = 2024
 ) a
inner join 
(select
	distinct mch_nm
from
	cmb_usr_trx_rcd
where 
	usr_id = '5211314521'
and year(trx_time) = 2024
 ) b on a.mch_nm = b.mch_nm
 order by 1 desc
2025-01-02 表连接(1)你们难道都去过?那就试试用InnerJoin 
select 
	*
from
(select
	distinct mch_nm
from
	cmb_usr_trx_rcd
where 
	usr_id = '5201314520'
and year(trx_time) = 2024
 ) a
inner join 
(select
	distinct mch_nm
from
	cmb_usr_trx_rcd
where 
	usr_id = '5211314521'
and year(trx_time) = 2024
 ) b on a.mch_nm = b.mch_nm
 order by 1 desc
2025-01-02 表连接(1)你们难道都去过?那就试试用InnerJoin 
select 
	*
from
(select
	distinct mch_nm
from
	cmb_usr_trx_rcd
where 
	usr_id = '5201314520'
and year(trx_time) = 2024
 ) a
inner join 
(select
	distinct mch_nm
from
	cmb_usr_trx_rcd
where 
	usr_id = '5211314521'
and year(trx_time) = 2024
 ) b on a.mch_nm = b.mch_nm
 order by 1
2025-01-02 子查询(1)玩的最嗨那天在做甚?要用Where子查询 
select
	*
from
	cmb_usr_trx_rcd
where
	usr_id = '5201314520'
and year(trx_time) = 2024
and trx_amt = (
		select
			max(trx_amt) as max_trx_amt
		from
			cmb_usr_trx_rcd
		where
			usr_id = '5201314520'
			and year(trx_time) = 2024
	)
2024-12-31 字符串与通配符(2)好多关键词做规则,可以使用rlike 
select
	case when mch_nm like '%按摩保健休闲%'then '按摩保健休闲' 
	when mch_nm like '%按摩%'then '按摩、保健、休闲、养生、SPA、会所'
		when mch_nm like '%保健%'then '按摩、保健、休闲、养生、SPA、会所'
	when mch_nm like '%休闲%'then '按摩、保健、休闲、养生、SPA、会所'
	when mch_nm like '%养生%'then '按摩、保健、休闲、养生、SPA、会所'
	when mch_nm like '%SPA%'then '按摩、保健、休闲、养生、SPA、会所'
	when mch_nm like '%会所%'then '按摩、保健、休闲、养生、SPA、会所'
	else '其他' end as reg_rules,
	count(distinct mch_nm) as mch_cnt
from
	cmb_usr_trx_rcd
where
	mch_nm like '%按摩保健休闲%'
or upper(mch_nm) rlike '.*(按摩|保健|休闲|SPA|养生|会所).*'
group by
 reg_rules
 order by
 	 mch_cnt desc
2024-12-31 字符串与通配符(2)好多关键词做规则,可以使用rlike 
select
	case when mch_nm like '%按摩%'then '按摩、保健、休闲、养生、SPA、会所'
		when mch_nm like '%保健%'then '按摩、保健、休闲、养生、SPA、会所'
	when mch_nm like '%休闲%'then '按摩、保健、休闲、养生、SPA、会所'
	when mch_nm like '%养生%'then '按摩、保健、休闲、养生、SPA、会所'
	when mch_nm like '%SPA%'then '按摩、保健、休闲、养生、SPA、会所'
	when mch_nm like '%会所%'then '按摩、保健、休闲、养生、SPA、会所'
	when mch_nm like '%按摩保健休闲%'then '按摩保健休闲'
	else '其他' end as reg_rules,
	count(distinct mch_nm) as mch_cnt
from
	cmb_usr_trx_rcd
group by
 reg_rules
 order by
 	 mch_cnt desc
2024-12-31 字符串与通配符(1)名称里面有特服,可以使用通配符 
select
	count(distinct mch_nm) as mch_nm_num
from 
	cmb_usr_trx_rcd
where
	mch_nm like '%按摩保健休闲%'
2024-12-31 字符串与通配符(1)名称里面有特服,可以使用通配符 
select
	count(mch_nm) as mch_nm_num
from 
	cmb_usr_trx_rcd
where
	mch_nm like '%按摩保健休闲%'
2024-12-31 分类(1)姿势太多很过分,分类要用CaseWhen 
select
	case when trx_amt = 288 then '1.WithHand'
		when trx_amt = 388 then '2.WithMimi'
	when trx_amt = 588 then '3.BlowJobbie'
	when trx_amt = 888 then '4.Doi'
	when trx_amt = 1288 then '5.DoubleFly'
	else '6.other' end as ser_typ,
count(trx_amt) as trx_cnt,
min(date(trx_time)) as first_date
from 
	cmb_usr_trx_rcd
where
	mch_nm = '红玫瑰按摩保健休闲'
and usr_id = '5201314520'
group by
	ser_typ
order by
	ser_typ asc
2024-12-31 分组与聚合函数(6)想知道渣男有多坏,疯狂使用GroupBy 
select
	usr_id,
mch_nm,
sum(trx_amt) as trx_amt,
count(trx_amt) as trx_cnt,
min(trx_time) as first_time
from 
	cmb_usr_trx_rcd
where
	usr_id = '5201314520'
and trx_amt >= 288
group by
	mch_nm
order by
	trx_cnt desc
2024-12-31 分组与聚合函数(5)想知道何时成瘾,用Max Or Min? 
select
	usr_id,
min(trx_time) as frist_time,
mch_nm
from
	cmb_usr_trx_rcd
where
	mch_nm = '红玫瑰按摩保健休闲'
and usr_id = '5201314520'
2024-12-31 分组与聚合函数(5)五花八门的项目,其实都有固定套路(2) 
select
	trx_amt,
count(1) as total_trx_cnt,
count(distinct usr_id) as unique_usr_cnt,
count(1) / count(distinct usr_id) as avg_trx_per_user
from
	cmb_usr_trx_rcd
where
	mch_nm = '红玫瑰按摩保健休闲'
and(
(year(trx_time) = 2023 and month(trx_time) between 1 and 12)
or (year(trx_time) = 2024 and month(trx_time) between 1 and 6) 
)
group by
	trx_amt
order by
	avg_trx_per_user desc 
limit 5
2024-12-31 分组与聚合函数(5)五花八门的项目,其实都有固定套路(2) 
select
	trx_amt,
count(1) as total_trx_cnt,
count(distinct usr_id) as unique_usr_cnt,
count(1) / count(distinct usr_id) as avg_trx_per_user
from
	cmb_usr_trx_rcd
where
	mch_nm = '红玫瑰按摩保健休闲'
and (year(trx_time) = 2023 and month(trx_time) between 1 and 12)
or (year(trx_time) = 2024 and month(trx_time) between 1 and 6) 
group by
	trx_amt
order by
	avg_trx_per_user desc 
limit 5
2024-12-31 分组与聚合函数(5)五花八门的项目,其实都有固定套路(2) 
select
	trx_amt,
count(trx_amt) as total_trx_cnt,
count(distinct usr_id) as unique_usr_cnt,
count(trx_amt) / count(distinct usr_id) as avg_trx_per_user
from
	cmb_usr_trx_rcd
where
	mch_nm = '红玫瑰按摩保健休闲'
and (year(trx_time) = 2023 and month(trx_time) between 1 and 12)
or (year(trx_time) = 2024 and month(trx_time) between 1 and 6) 
group by
	trx_amt
order by
	avg_trx_per_user desc 
limit 5
2024-12-31 分组与聚合函数(5)五花八门的项目,其实都有固定套路(2) 
select
	trx_amt,
count(trx_amt) as total_trx_cnt,
count(distinct usr_id) as unique_usr_cnt,
count(trx_amt) / count(distinct usr_id) as avg_trx_per_user
from
	cmb_usr_trx_rcd
where
	mch_nm = '红玫瑰按摩保健休闲'
and year(trx_time) = 2023
and month(trx_time) = 6
group by
	trx_amt
order by
	avg_trx_per_user desc 
limit 5
2024-12-30 分组与聚合函数(3)五花八门的项目,其实都有固定套路(1) 
select
	trx_amt,
count(trx_amt) as trx_cnt
from 
	cmb_usr_trx_rcd
where
	mch_nm = '红玫瑰按摩保健休闲'
and year(trx_time) = 2024
and month(trx_time) between 1 and 7
group by
	trx_amt
order by
	trx_cnt desc
limit 5
2024-12-30 分组与聚合函数(3)五花八门的项目,其实都有固定套路(1) 
select
	trx_amt,
count(trx_amt) as trx_cnt
from 
	cmb_usr_trx_rcd
where
	mch_nm = '红玫瑰按摩保健休闲'
and year(trx_time) = 2024
and month(trx_time) between 1 and 7
group by
	trx_amt
order by
	trx_cnt desc
2024-12-30 分组与聚合函数(2)擦边营收怎么样,聚合函数可看出 
select
	date(trx_time) as trx_date,
max(trx_amt) as max_trx_amt,
min(trx_amt) as min_trx_amt,
avg(trx_amt) as avg_trx_amt,
sum(trx_amt) as sum_trx_amt
from
	cmb_usr_trx_rcd
where
	mch_nm = '红玫瑰按摩保健休闲'
and year(trx_time) = 2024
and month(trx_time) = 9
group by
	date(trx_time)
order by
	trx_date asc