全站第 55/1260 名

解决了 22/335 题

中等: 0/76
入门: 16/77
困难: 0/29
简单: 4/114
草履虫: 2/39

过去1年一共提交 53 次

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

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

达成1题里程碑2024-12-24
达成2题里程碑2024-12-24
达成5题里程碑2024-12-24
达成10题里程碑2024-12-25
达成20题里程碑2025-01-23
完成w12打卡2025-01-23
完成w24打卡2025-03-07
完成w25打卡2025-02-08

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-01-20 表连接（2）渣男去过我对象没去过，那就用LeftJoin  
select a.mch_nm as asshole_tried, a.trx_cnt, b.mch_nm as darling_tried 
from
(select usr_id, mch_nm, count(trx_time) as trx_cnt  from cmb_usr_trx_rcd 
where usr_id = '5201314520' and year(trx_time) in ('2023', '2024')
group by mch_nm
having count(trx_time) >= 20) as a
left join 
(select distinct mch_nm from cmb_usr_trx_rcd where usr_id = '5211314521' and year(trx_time) in ('2023', '2024'))
as b
on a.mch_nm = b.mch_nm
order by a.trx_cnt desc
 
啥也没说
2024-12-31 用户"kjhd30"的第一笔未完成订单  
select * from didi_order_rcd
where cust_uid = 'kjhd30'
and finish_time = '1970-01-01 00:00:00'
order by call_time 
limit 1
 
啥也没说
2024-12-31 子查询（1）玩的最嗨那天在做甚？要用Where子查询  
select * from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) = '2024' 
order by trx_amt desc
limit 1
 
啥也没说
2024-12-25 分类（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(1) as trx_cnt,
       min(date(trx_time)) as first_date
from cmb_usr_trx_rcd
where 
    usr_id = '5201314520'
and mch_nm = '红玫瑰按摩保健休闲'
group by ser_typ
order by ser_typ 

点解不对
 
啥也没说
2024-12-25 分组与聚合函数（6）想知道渣男有多坏，疯狂使用GroupBy  
select 
      usr_id,
      mch_nm,
      sum(trx_amt) as trx_amt,
      count(1) as trx_cnt,
      min(trx_time) as first_time
from
    cmb_usr_trx_rcd
where 
    usr_id = '5201314520'
and trx_amt >= 288
group by usr_id, mch_nm
order by trx_cnt desc
 
啥也没说
2024-12-25 分组与聚合函数（6）想知道渣男有多坏，疯狂使用GroupBy  
select 
      usr_id,
      mch_nm,
      sum(trx_amt) as trx_amt,
      count(1) as trx_cnt,
      min(trx_time) as first_time
from
    cmb_usr_trx_rcd
where 
    usr_id = '5201314520'
and trx_amt >= 288
group by usr_id, mch_nm
order by trx_cnt desc
 
啥也没说
2024-12-25 分组与聚合函数（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 date(trx_time) between '2023-1-1' and '2024-6-30'
 and mch_nm = '红玫瑰按摩保健休闲'
 group by trx_amt
 order by avg_trx_per_user desc
 limit 5
 
啥也没说
2024-12-25 分组与聚合函数（5）想知道何时成瘾，用Max Or Min？  
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 date(trx_time) between '2023-1-1' and '2024-6-30'
 and mch_nm = '红玫瑰按摩保健休闲'
 group by trx_amt
 order by avg_trx_per_user desc
 limit 5
 
啥也没说
2024-12-25 分组与聚合函数（3）五花八门的项目，其实都有固定套路（1）  
select trx_amt, count(*) as trx_cnt
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
and date(trx_time) between '2024-1-1' and '2024-7-31'
group by trx_amt
order by trx_cnt desc
limit 5
 
啥也没说
2024-12-24 分组与聚合函数（2）擦边营收怎么样，聚合函数可看出  
select date(trx_time),
       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 cmb_usr_trx_rcd 
where mch_nm = '红玫瑰按摩保健休闲' 
and usr_id = '5201314520'
and year(trx_time) = '2024'
and month(trx_time) = '9'
group by date(trx_time)
order by date(trx_time)

点解这个不对啊555
 
题干要求研究的是这个商户，不是渣男

提交记录

提交日期 题目名称 提交代码
2025-03-07 德州扑克起手牌-同花  
select sum(case when right(card1, 1)= right(card2, 1) then 1 else 0 end)/2 as cnt,count(1)/2 as ttl_cnt,
sum(case when right(card1, 1)= right(card2, 1) then 1 else 0 end) / count(1) as p
from hand_permutations
2025-03-07 德州扑克起手牌-同花  
select sum(case when right(card1, 1)= right(card2, 1) then 1 else 0 end)/2 as cnt,count(1) as ttl_cnt,
sum(case when right(card1, 1)= right(card2, 1) then 1 else 0 end) / count(1) as p
from hand_permutations
2025-03-07 德州扑克起手牌-同花  
select 
    sum(case when right(card1,1)=right(card2,1) then 1 else 0 end) as cnt 
    , count(1) 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-03-07 德州扑克起手牌- 手对  
select * from hand_permutations
 where left(card1, 1) = left(card2, 1)
 order by id
2025-03-06 条件过滤（1）异地男友有异常，数分闺蜜来帮忙  
SELECT *
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520'
AND trx_time BETWEEN '2024-09-01 00:00:00' AND '2024-09-30 23:59:59';
2025-02-08 德州扑克起手牌- A花  
select * from hand_permutations
where right(card1, 1)= right(card2,1) and (card1 like 'A%' or card2 like 'A%')
order by id
2025-01-23 德州扑克起手牌-最强起手牌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;
2025-01-23 抖音面试真题（1）T+1日留存率  
with At as (
select distinct usr_id,
 date(login_time) as login_date
from user_login_log
where datediff(curdate(), login_time) <= 30
)
select At.login_date,
 concat(round(count(distinct Bt.usr_id)/count(distinct At.usr_id)*100, 2), '%') as T1_retention_rate
from At
 left join At Bt on At.usr_id = Bt.usr_id
and datediff(Bt.login_date, At.login_date) = 1
group by At.login_date
2025-01-20 销售金额前10的商品信息  
select goods_id, sum(order_gmv) as total_gmv from order_info 
where date(order_time) = '2024-09-10'
group by goods_id
order by total_gmv desc
limit 10
2025-01-20 销售金额前10的商品信息  
select * from order_info 
where date(order_time) = '2024-09-10'
order by order_gmv desc
limit 10
2025-01-20 表连接（2）渣男去过我对象没去过，那就用LeftJoin  
select a.mch_nm as asshole_tried, a.trx_cnt, b.mch_nm as darling_tried 
from
(select usr_id, mch_nm, count(trx_time) as trx_cntfrom cmb_usr_trx_rcd 
where usr_id = '5201314520' and year(trx_time) in ('2023', '2024')
group by mch_nm
having count(trx_time) >= 20) as a
left join 
(select distinct mch_nm from cmb_usr_trx_rcd where usr_id = '5211314521' and year(trx_time) in ('2023', '2024'))
as b
on a.mch_nm = b.mch_nm
order by a.trx_cnt desc
2025-01-20 表连接（2）渣男去过我对象没去过，那就用LeftJoin  
select a.mch_nm as asshole_tried, a.trx_cnt, b.mch_nm as darling_tried 
from
(select usr_id, mch_nm, count(trx_time) as trx_cntfrom cmb_usr_trx_rcd 
where usr_id = '5201314520' and year(trx_time) in ('2023', '2024')
group by mch_nm
having count(trx_time) >= 20) as a
left join 
(select * from cmb_usr_trx_rcd where usr_id = '5211314521' and year(trx_time) in ('2023', '2024'))
as b
on a.mch_nm = b.mch_nm
order by a.trx_cnt desc
2025-01-20 表连接（2）渣男去过我对象没去过，那就用LeftJoin  
select a.mch_nm, a.trx_cnt, b.mch_nm as darling_tried 
from
(select usr_id, mch_nm, count(trx_time) as trx_cntfrom cmb_usr_trx_rcd 
where usr_id = '5201314520' and year(trx_time) in ('2023', '2024')
group by mch_nm
having count(trx_time) >= 20) as a
left join 
(select * from cmb_usr_trx_rcd where usr_id = '5211314521' and year(trx_time) in ('2023', '2024'))
as b
on a.mch_nm = b.mch_nm
2024-12-31 找出所有港台乐队  
select * from singer_info
where type2 = '港台' and type3 = '乐队'
2024-12-31 用户"kjhd30"的第一笔未完成订单  
select * from didi_order_rcd
where cust_uid = 'kjhd30'
and finish_time = '1970-01-01 00:00:00'
order by call_time 
limit 1
2024-12-31 表连接（1）你们难道都去过？那就试试用InnerJoin  
select distinct a.* from 
(selectmch_nm
from cmb_usr_trx_rcd
where year(trx_time)=2024 and usr_id='5211314521')a
inner join
(selectmch_nm
from cmb_usr_trx_rcd
where year(trx_time)=2024 and usr_id='5201314520')b
on a.mch_nm = b.mch_nm
order by 1 desc
2024-12-31 表连接（1）你们难道都去过？那就试试用InnerJoin  
select distinct a.mch_nm from 
(select mch_nm from cmb_usr_trx_rcd 
where usr_id ='5201314520' and year(trx_time)= '2024') as a 
inner join
(select mch_nm from cmb_usr_trx_rcd 
where usr_id ='5211314521' and year(trx_time)= '2024') as b
on a.mch_nm=b.mch_nm
2024-12-31 表连接（1）你们难道都去过？那就试试用InnerJoin  
select distinct a.mch_nm from 
(select mch_nm from cmb_usr_trx_rcd 
where usr_id ='5201314520') as a 
inner join
(select mch_nm from cmb_usr_trx_rcd 
where usr_id ='5211314521') as b
on a.mch_nm=b.mch_nm
2024-12-31 子查询（1）玩的最嗨那天在做甚？要用Where子查询  
select * from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) = '2024' 
order by trx_amt desc
limit 1
2024-12-31 字符串与通配符（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;