排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2024-11-15 统计每个用户使用过的不同车型数量 
select cust_uid, count(distinct car_cls) as unique_car_classes
from didi_sht_rcd
group by cust_uid
order by unique_car_classes desc
2024-11-15 查询所有终点是餐饮类地点的行程记录 
SELECT *
FROM didi_sht_rcd a
left JOIN loc_nm_ctg b ON a.end_loc = b.loc_nm
where b.loc_ctg = '餐饮'
ORDER BY a.start_tm ASC;
2024-11-15 查询所有终点是餐饮类地点的行程记录 
SELECT a.*
FROM didi_sht_rcd a
left JOIN loc_nm_ctg b ON a.end_loc = b.loc_nm
where b.loc_ctg = '餐饮'
ORDER BY a.start_tm ASC;
2024-11-15 查询所有终点是餐饮类地点的行程记录 
select a.*
from didi_sht_rcd a 
 join loc_nm_ctg b
on a.end_loc = b.loc_nm
where b.loc_ctg = '餐厅'
order by a.start_tm desc
2024-11-15 查询所有终点是餐饮类地点的行程记录 
select a.*
from didi_sht_rcd a 
left join loc_nm_ctg b
on a.end_loc = b.loc_nm
where b.loc_ctg = '餐厅'
order by a.start_loc
2024-11-15 查询所有终点是餐饮类地点的行程记录 
select *
from didi_sht_rcd a 
left join loc_nm_ctg b
on a.end_loc = b.loc_nm
where b.loc_ctg = '餐厅'
order by a.start_loc
2024-11-15 按照车类统计行程次数 
SELECT car_cls, COUNT(car_cls ) AS trip_count
FROM didi_sht_rcd
GROUP BY car_cls
ORDER BY trip_count DESC;
2024-11-15 按照车类统计行程次数 
SELECT car_cls, COUNT(distinct cust_uid ) AS trip_count
FROM didi_sht_rcd
GROUP BY car_cls
ORDER BY trip_count DESC;
2024-11-13 表连接(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 
where b.mch_nm is null
order by 2 desc
2024-11-13 表连接(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 2 desc
2024-11-13 表连接(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 2 desc
2024-11-13 表连接(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 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 
where b.mch_nm is null
order by 2 desc
2024-11-13 表连接(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 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 2 desc
2024-11-13 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select 
    a.mch_nm as asshole_tried,  
    a.trx_cnt,                  
    b.mch_nm as darling_tried   
from 
(select distinct
    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 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 2 desc
2024-11-13 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select
	a.mch_nm,tired
from
	(select mch_nm,
 	count(1) as tired
from cmb_usr_trx_rcd
where
	usr_id = 5201314520
	and year(trx_time) in (2023,2024)
 group by mch_nm
having tired >= 20 )a
left join
	(select mch_nm
from cmb_usr_trx_rcd
where
	usr_id = 52113145521
and year(trx_time) in (2023,2024))b
on
	a.mch_nm != b.mch_nm
order by
	2 desc
2024-11-13 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select
	*
from
	(select mch_nm,
 	count(1) as tired
from cmb_usr_trx_rcd
where
	usr_id = 5201314520
	and year(trx_time) in (2023,2024)
 group by mch_nm
having tired >= 20 )a
left join
	(select mch_nm
from cmb_usr_trx_rcd
where
	usr_id = 52113145521
and year(trx_time) in (2023,2024))b
on
	a.mch_nm != b.mch_nm
order by
	1 desc
2024-11-13 表连接(1)你们难道都去过?那就试试用InnerJoin 
select 
	distinct *
from
	(select mch_nm
from
	cmb_usr_trx_rcd
 where
 	usr_id = 5211314521
 and year(trx_time)=2024
)a
inner join
	(select mch_nm
from	
 		cmb_usr_trx_rcd
 where
 	usr_id = 5201314520
and year(trx_time)=2024
)b
on 
	a.mch_nm = b.mch_nm
order by
1 desc
2024-11-13 表连接(1)你们难道都去过?那就试试用InnerJoin 
select 
	distinct a.*
from
	(select mch_nm
from
	cmb_usr_trx_rcd
 where
 	usr_id = 5211314521
 and year(trx_time)=2024
)a
inner join
	(select mch_nm
from	
 		cmb_usr_trx_rcd
 where
 	usr_id = 5201314520
and year(trx_time)=2024
)b
on 
	a.mch_nm = b.mch_nm
order by
1 desc
2024-11-13 表连接(1)你们难道都去过?那就试试用InnerJoin 
select 
	a.*
from
	(select distinct mch_nm
from
	cmb_usr_trx_rcd
 where
 	usr_id = 5211314521
 and year(trx_time)=2024
)a
inner join
	(select distinct mch_nm
from	
 		cmb_usr_trx_rcd
 where
 	usr_id = 5201314520
and year(trx_time)=2024
)b
on 
	a.mch_nm = b.mch_nm
order by
1 desc
2024-11-13 表连接(1)你们难道都去过?那就试试用InnerJoin 
select 
	a.*
from
	(select distinct mch_nm
from
	cmb_usr_trx_rcd
 where
 	usr_id = 5211314521
 and year(trx_time)=2024
)a
left join
	(select distinct mch_nm
from	
 		cmb_usr_trx_rcd
 where
 	usr_id = 5201314520
and year(trx_time)=2024
)b
on 
	a.mch_nm = b.mch_nm
order by
1 desc