排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-11-14 分类(1)姿势太多很过分,分类要用CaseWhen 
正确的代码应该是怎么样的呀?我只看到了一处陷阱max->min,但还是报错。求讲解
同学,这句话暴露了你基础非常非常薄弱哦!!这个题既考差了case when,也考察了group by,原答案里没有group by😊

提交记录

提交日期 题目名称 提交代码
2024-11-14 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select
 a.mch_nm as asshole_tried,
 a.trx_cnt as asshole_tried_cnt,
 b.mch_nm as darling_tried
from
(select 
 mch_nm,
 count(*) 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(mch_nm)>=20) a
left join
(select 
 distinct mch_nm
from
 cmb_usr_trx_rcd
where
 year(trx_time) in (2023,2024)
 and usr_id='5211414521') b
on 
a.mch_nm=b.mch_nm
order by
 2 desc
2024-11-14 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select
 a.mch_nm as asshole_tried,
 a.trx_cnt as asshole_tried_cnt,
 b.mch_nm as darling_tried
from
(select 
 mch_nm,
 count(*) 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(mch_nm)>=20) a
left join
(select 
 distinct mch_nm
from
 cmb_usr_trx_rcd
where
 year(trx_time) in (2023,2024)
 and usr_id='5211414521') b
on 
a.mch_nm=b.mch_nm
order by
 asshole_tried_cnt desc
2024-11-14 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select
 a.mch_nm as asshole_tried,
 a.trx_cnt as asshole_tried_cnt,
 b.mch_nm as darling_tried
from
(select 
 mch_nm,
 count(*) 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(mch_nm)>=20) a
left join
(select 
 distinct mch_nm
from
 cmb_usr_trx_rcd
where
 year(trx_time) in (2023,2024)
 and usr_id='5211414521'
group by 
 mch_nm
having
 count(mch_nm)=0) b
on 
a.mch_nm=b.mch_nm
order by
 asshole_tried_cnt desc
2024-11-14 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select
 a.mch_nm as asshole_tried,
 a.trx_cnt as asshole_tried_cnt,
 b.mch_nm as darling_tried
from
(select 
 mch_nm,
 count(*) 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(mch_nm)>=20) a
left join
(select 
 mch_nm
from
 cmb_usr_trx_rcd
where
 year(trx_time) in (2023,2024)
 and usr_id='5211414521'
group by 
 mch_nm
having
 count(mch_nm)=0) b
on 
a.mch_nm=b.mch_nm
order by
 asshole_tried_cnt desc
2024-11-14 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select
 a.mch_nm as asshole_tried,
 a.trx_cnt as asshole_tried_cnt,
 b.mch_nm as darling_tried
from
(select 
 mch_nm,
 count(*) 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(mch_nm)>20) a
left join
(select 
 mch_nm
from
 cmb_usr_trx_rcd
where
 year(trx_time) in (2023,2024)
 and usr_id='5211414521'
group by 
 mch_nm
having
 count(mch_nm)=0) b
on 
a.mch_nm=b.mch_nm
order by
 asshole_tried_cnt desc
2024-11-14 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select
 a.mch_nm as asshole_tried,
 a.trx_cnt as asshole_tried_cnt,
 b.mch_nm as darling_tried
from
(select 
 mch_nm,
 count(*) as trx_cnt
from
 cmb_usr_trx_rcd
where
 year(trx_time) in (2023,2024)
 and usr_id='5201314520'
group by 
 mch_nm
having
 count(mch_nm)>20) a
left join
(select 
 mch_nm
from
 cmb_usr_trx_rcd
where
 year(trx_time) in (2023,2024)
 and usr_id='5211414521'
group by 
 mch_nm
having
 count(mch_nm)=0) b
on 
a.mch_nm=b.mch_nm
order by
 asshole_tried_cnt desc
2024-11-14 表连接(1)你们难道都去过?那就试试用InnerJoin 
select
 mch_nm
from
 cmb_usr_trx_rcd
where
 usr_id='5201314520'
 and mch_nm in (select mch_nm from cmb_usr_trx_rcd where usr_id='5211414521')
2024-11-14 分类(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 trx_cnt,
min(date(trx_time)) as first_date
from 
cmb_usr_trx_rcd
where
usr_id='5201314520'
and mch_nm='红玫瑰按摩保健休闲'
group by
trx_amt
order by
ser_typ
2024-11-14 子查询(1)玩的最嗨那天在做甚?要用Where子查询 
select 
 *
from 
 cmb_usr_trx_rcd
where
 trx_amt=(select
 max(trx_amt) 
from
 cmb_usr_trx_rcd
where
 year(trx_time)=2024
 and usr_id='5201314520'
 )
 and usr_id='5201314520'
 and year(trx_time)=2024
2024-11-14 子查询(1)玩的最嗨那天在做甚?要用Where子查询 
select 
 usr_id,
 mch_nm,
 trx_time,
 trx_amt
from 
 cmb_usr_trx_rcd
where
 usr_id='5201314520'
 and year(trx_time)='2024'
order by
 trx_amt desc
limit 1
2024-11-14 子查询(1)玩的最嗨那天在做甚?要用Where子查询 
select 
 usr_id,
 mch_nm,
 trx_time,
 trx_amt
from 
 cmb_usr_trx_rcd
where
 usr_id='5201314520'
order by
 trx_amt desc
limit 1
2024-11-14 字符串与通配符(1)名称里面有特服,可以使用通配符 
select
count(distinct(mch_nm)) as mch_cnt
from
cmb_usr_trx_rcd
where
mch_nm like '%按摩保健休闲%'
2024-11-14 分类(1)姿势太多很过分,分类要用CaseWhen 
select
case
when trx_amt=288 then '1-With Hand'
when trx_amt=388 then '2-With Mimi'
when trx_amt=588 then '3-Blow Jobbie'
when trx_amt=888 then '4-Do i'
when trx_amt=1288 then '5-Double Fly'
else '6-other'
end as ser_typ,
count(*) as trx_cnt,
min(date(trx_time)) as first_date
from
cmb_usr_trx_rcd
where 
usr_id='5201314520'
and mch_nm='红玫瑰按摩保健休闲'
group by
trx_amt
order by
ser_typ
2024-11-14 分类(1)姿势太多很过分,分类要用CaseWhen 
select
case
when trx_amt=288 then '1-With Hand'
when trx_amt=388 then '2-With Mimi'
when trx_amt=588 then '3-Blow Jobbie'
when trx_amt=888 then '4-Do i'
when trx_amt=1288 then '5-Double Fly'
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
trx_amt
order by
ser_typ
2024-11-14 分类(1)姿势太多很过分,分类要用CaseWhen 
select
case
when trx_amt=288 then '1-With Hand'
when trx_amt=388 then '2-With Mimi'
when trx_amt=588 then '3-Blow Jobbie'
when trx_amt=888 then '4-Do i'
when trx_amt=1288 then '5-Double Fly'
else '6-other'
end as ser_typ,
count(1) as trx_cnt,
min(trx_time) as first_date
from
cmb_usr_trx_rcd
where 
usr_id='5201314520'
and mch_nm='红玫瑰按摩保健休闲'
group by
trx_amt
order by
ser_typ
2024-11-14 分组与聚合函数(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-11-14 分组与聚合函数(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
2024-11-14 分组与聚合函数(5)想知道何时成瘾,用Max Or Min? 
select
    usr_id,
    trx_time as first_time,  
    mch_nm
from
    cmb_usr_trx_rcd
where
    mch_nm = '红玫瑰按摩保健休闲'
    and usr_id = '5201314520'
order by
trx_time
limit 1
2024-11-14 分组与聚合函数(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
2024-11-14 分组与聚合函数(5)五花八门的项目,其实都有固定套路(2) 
select 
trx_amt,
count(*) as total_trx_cnt,
count(distinct(usr_id)) as unique_usr_cnt,
count(*)/count(distinct(usr_id)) as avg_trx_per_user 
from 
cmb_usr_trx_rcd 
where 
 mch_nm='红玫瑰按摩保健休闲' 
 and 
 date(trx_time) between '2023-01-01' and '2024-06-30' 
group by
 trx_amt
order by
 avg_trx_per_user desc
limit 5