排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-01-13 表连接（1）你们难道都去过？那就试试用InnerJoin  
select a.mch_nm
from
(select distinct(mch_nm)
from cmb_usr_trx_rcd
where usr_id='5201314520' and year(trx_time)=2024 ) b
inner join
(select distinct(mch_nm)
from cmb_usr_trx_rcd
where usr_id='5211314521' and year(trx_time)=2024) a
using(mch_nm)
order by mch_nm desc
2025-01-13 表连接（1）你们难道都去过？那就试试用InnerJoin  
select b.mch_nm
from
(select distinct(mch_nm)
from cmb_usr_trx_rcd
where usr_id='5201314520' and year(trx_time)=2024 ) b
inner join
(select distinct(mch_nm)
from cmb_usr_trx_rcd
where usr_id='5211314521' and year(trx_time)=2024) a
using(mch_nm)
order by mch_nm desc
2025-01-13 表连接（1）你们难道都去过？那就试试用InnerJoin  
select b.mch_nm
from
(select distinct(mch_nm)
from cmb_usr_trx_rcd
where usr_id='5201314520' and year(trx_time)=2024 ) b
join
(select distinct(mch_nm)
from cmb_usr_trx_rcd
where usr_id='5211314521' and year(trx_time)=2024) a
using(mch_nm)
order by mch_nm desc
2025-01-13 表连接（1）你们难道都去过？那就试试用InnerJoin  
select t2.mch_nm
from 
(SELECT DISTINCT usr_id, mch_nm
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520 AND trx_time LIKE "2024%") t1
inner join 
(SELECT DISTINCT usr_id, mch_nm
FROM cmb_usr_trx_rcd
WHERE usr_id=5211314521 AND trx_time LIKE "2024%") t2
on t1.mch_nm=t2.mch_nm
2025-01-13 子查询（1）玩的最嗨那天在做甚？要用Where子查询  
select * 
from cmb_usr_trx_rcd 
where usr_id=5201314520 and mch_nm rlike ".*(按摩|保健|休闲|SPA|养生|会所).*"
order by trx_amt desc 
limit 1
2025-01-13 字符串与通配符（2）好多关键词做规则，可以使用rlike  
select
    case 
        when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
        when upper(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 upper(mch_nm) rlike '.*(按摩|保健|休闲|SPA|养生|会所).*'
group by reg_rules
order by mch_cnt desc
2025-01-13 字符串与通配符（1）名称里面有特服，可以使用通配符  
select count(distinct mch_nm) as mch_cnt
from 
cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
2025-01-13 分类（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(*) AS ser_typ_count,
MIN(trx_date) AS first_date 
FROM (
SELECT
DATE(trx_time) AS trx_date, trx_amt
FROM cmb_usr_trx_rcd 
WHERE usr_id = '5201314520' 
AND mch_nm = '红玫瑰按摩保健休闲'
) t
GROUP BY ser_typ
order by ser_typ
2025-01-13 分类（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 "other"
END AS ser_typ,
COUNT(*) AS ser_typ_count,
MIN(trx_date) AS first_date 
FROM (
SELECT
DATE(trx_time) AS trx_date, trx_amt
FROM cmb_usr_trx_rcd 
WHERE usr_id = '5201314520' 
AND mch_nm = '红玫瑰按摩保健休闲'
) t
GROUP BY ser_typ
order by ser_typ
2025-01-13 分组与聚合函数（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(select * from cmb_usr_trx_rcd 
where usr_id=5201314520 
and trx_amt>=288) t 
group by mch_nm
order by trx_cnt desc 
limit 5
2025-01-13 分组与聚合函数（5）想知道何时成瘾，用Max Or Min？  
select usr_id,trx_time as first_time,mch_nm
from cmb_usr_trx_rcd 
where usr_id=5201314520 and mch_nm = "红玫瑰按摩保健休闲"
order by trx_time
limit 1
2025-01-13 分组与聚合函数（3）五花八门的项目，其实都有固定套路（1）  
select trx_amt,count(1) as trx_cnt
from(select substring(trx_time,1,7) trx_time,trx_amt
from cmb_usr_trx_rcd 
where trx_time REGEXP '^2024-0[1-7]' 
and mch_nm = "红玫瑰按摩保健休闲") t
group by trx_amt
2025-01-13 分组与聚合函数（3）五花八门的项目，其实都有固定套路（1）  
select substring(trx_time,1,7)as trx_time
from cmb_usr_trx_rcd 
where trx_time REGEXP '^2020-0[1-7]'
2025-01-13 分组与聚合函数（2）擦边营收怎么样，聚合函数可看出  
select 
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 total_trx_amt
from(select date(trx_time) as trx_date ,trx_amt
from cmb_usr_trx_rcd 
where mch_nm = "红玫瑰按摩保健休闲" 
and trx_time like "2024-09%") t 
group by trx_date
order by trx_date
2025-01-13 分组与聚合函数（1）Money全都花在哪，GroupBy来查一查  
select mch_nm,
sum(trx_amt) sum_trx_amt
from (select *
from cmb_usr_trx_rcd 
where usr_id=5201314520 and trx_time like "2024-%") t 
group by usr_id,mch_nm
order by sum_trx_amt desc
2025-01-13 分组与聚合函数（1）Money全都花在哪，GroupBy来查一查  
select mch_nm,
sum(trx_amt) sum_trx_amt
from (select *
from cmb_usr_trx_rcd 
where usr_id=5201314520 and trx_time like "2024-%") t 
group by usr_id,mch_nm
2025-01-13 条件过滤（3）Hour函数很给力，组合条件要仔细  
select
    *
from
    cmb_usr_trx_rcd
where
    date(trx_time)  between '2024-09-01' and '2024-09-30' 
    and hour(trx_time) in (22,23,0,1,2,3,4)
    and usr_id = '5201314520' 
order by trx_time
2025-01-13 条件过滤（3）Hour函数很给力，组合条件要仔细  
select
    *
from
    cmb_usr_trx_rcd
where
    date(trx_time)  between '2024-09-01' and '2024-09-30' 
    and hour(trx_time) between 22 and 23 or hour(trx_time) between 1 and 4
    and usr_id = '5201314520' 
order by trx_time
2025-01-13 条件过滤（3）Hour函数很给力，组合条件要仔细  
select
    *
from
    cmb_usr_trx_rcd
where
    date(trx_time)  between '2024-09-01' and '2024-09-30' 
    and hour(trx_time) between 22 and 23 or hour(trx_time) between 0 and 4
    and usr_id = '5201314520' 
order by trx_time
2025-01-13 条件过滤（3）Hour函数很给力，组合条件要仔细  
select
    *
from
    cmb_usr_trx_rcd
where
    date(trx_time)  between '2024-09-01' and '2024-09-30' 
    and hour(trx_time) between 22 and 23 or hour(trx_time) between 0 and 04
    and usr_id = '5201314520' 
order by trx_time