排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2024-11-27 销售金额前10的商品信息 
select 
	goods_id,sum(order_gmv) total_gmv,
rank() over (order by sum(order_gmv) desc) as ranking
from order_info
where date(order_time)='2024-09-10'
group by goods_id
order by total_gmv desc
limit 10
2024-11-11 销售金额前10的商品信息 
select 
	a.order_id order_id,
a.total_gmv total_gmv,
	rank() over (order by a.total_gmv desc) ranking
from 
(select 
    order_id, 
    sum(order_gmv) as total_gmv
from 
    order_info
where 
    date(order_time) = '2024-09-10'
group by 
    order_id) a
order by total_gmv desc
limit 10;
2024-11-11 销售金额前10的商品信息 
select 
    order_id, 
    sum(order_gmv) as total_gmv,
    rank() over (order by sum(order_gmv) desc) as ranking
from 
    order_info
where 
    date(order_time) = '2024-09-10'
group by 
    order_id
order by 
    total_gmv desc
limit 10;
2024-11-08 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select 
    a.mch_nm as asshole_tried,  
    a.trx_cnt,                  
    b.mch_nm as darling_tried   
from 
(select 
    mch_nm,                   
    count(1) trx_cnt           
from cmb_usr_trx_rcd           
where 
    year(trx_time) in (2023, 2024)  
    and usr_id = '5201314520'  
group by mch_nm              
having count(1) >= 20) a       
left join 
(select distinct mch_nm     
from cmb_usr_trx_rcd          
where 
    year(trx_time) in (2023, 2024) 
    and usr_id = '5211314521') b 
on a.mch_nm = b.mch_nm 
order by a.trx_cnt desc
2024-11-08 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select 
    a.mch_nm as asshole_tried,  
    a.trx_cnt,                  
    b.mch_nm as darling_tried   
from 
(select 
    mch_nm,                     
    count(1) trx_cnt            
from cmb_usr_trx_rcd            
where 
    year(trx_time) in (2023, 2024)  
    and usr_id = '5201314520'   
group by mch_nm                 
having count(1) >= 20) a        
right join 
(select distinct mch_nm         
from cmb_usr_trx_rcd            
where 
    year(trx_time) in (2023, 2024)  
    and usr_id = '5211314521') b    
on a.mch_nm = b.mch_nm 
order by a.trx_cnt desc
2024-11-08 表连接(1)你们难道都去过?那就试试用InnerJoin 
select a.* from 
(select distinct mch_nm
from cmb_usr_trx_rcd
where year(trx_time)=2024 and usr_id='5211314521') a
inner join
(select distinct mch_nm
from cmb_usr_trx_rcd
where year(trx_time)=2024 and usr_id='5201314520') b
on a.mch_nm = b.mch_nm
2024-11-08 子查询(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-08 子查询(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 limit 1;
2024-11-08 字符串与通配符(1)名称里面有特服,可以使用通配符 
select count(distinct mch_nm) mch_cnt
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
2024-11-08 字符串与通配符(1)名称里面有特服,可以使用通配符 
select count(mch_nm) mch_cnt
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
2024-11-08 字符串与通配符(1)名称里面有特服,可以使用通配符 
select count(mch_nm) mch_cnt
from cmb_usr_trx_rcd
where usr_id=5201314520
and mch_nm like '%按摩保健休闲%'
2024-11-08 分类(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(trx_time) as first_date
from 
cmb_usr_trx_rcd
where usr_id='5201314520' and mch_nm='红玫瑰按摩保健休闲'
group by 
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
order by ser_typ desc
2024-11-08 分类(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(trx_time) as first_date
from 
cmb_usr_trx_rcd
where usr_id='5201314520' and mch_nm='红玫瑰按摩保健休闲'
group by 
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
order by trx_cnt desc
2024-11-08 分类(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(trx_time) as first_date
from 
cmb_usr_trx_rcd
where usr_id='5201314520' and mch_nm='红玫瑰按摩保健休闲'
group by 
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
order by trx_cnt
2024-11-08 分类(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(trx_time) as first_date
from 
cmb_usr_trx_rcd
where usr_id='5201314520' and mch_nm='红玫瑰按摩保健休闲'
group by 
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
order by ser_typ
2024-11-08 分类(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(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-11-08 分组与聚合函数(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 mch_nm
order by trx_cnt desc
2024-11-08 分组与聚合函数(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 mch_nm
order by trx_cnt
2024-11-08 分组与聚合函数(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
2024-11-08 分组与聚合函数(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