排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-03 窗口函数（5）越来越喜欢召妓，窗口函数用累计（3）  
抓个虫 一直没找到自己的代码逻辑错误，对比了评论区正确答案，好像还是目的要求里的“统计2023-Q1以来的数据”这个条件在最后平台给的代码里没修正
select a.trx_quarter, a.withand, b.doi
from
(select concat(year(trx_time),'-','Q',quarter(trx_time)) trx_quarter,
sum(sum(case when trx_amt=288 then 1 else 0 end)) over(order by concat(year(trx_time),'-','Q',quarter(trx_time)))withand
from cmb_usr_trx_rcd
where usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲'and date(trx_time)>='2023-01-01'
group by concat(year(trx_time),'-','Q',quarter(trx_time))
order by trx_quarter)a
join
(select concat(year(trx_time),'-','Q',quarter(trx_time)) trx_quarter,
sum(sum(case when trx_amt=888 then 1 else 0 end)) over(order by concat(year(trx_time),'-','Q',quarter(trx_time)))doi
from cmb_usr_trx_rcd
where usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲'and date(trx_time)>='2023-01-01'
group by concat(year(trx_time),'-','Q',quarter(trx_time))
order by trx_quarter)b on a.trx_quarter=b.trx_quarter
order by a.trx_quarter
（小声说这个解法还是太麻烦了）
 
笔芯♥️
2024-12-03 窗口函数（2）所有前一和每类前一，继续搞懂排序窗口函数  
什么原因导致代码错误，我代码输出结果中‘红玫瑰按摩保健休闲’的不区分年度总次数（代码运行是82）和参考输出（参考中是57）不一样
select *
from (select null trx_mon,mch_nm,count(trx_amt) trx_amt,dense_rank()over(order by count(trx_amt) desc) rnk
from cmb_usr_trx_rcd
where usr_id=5201314520
group by mch_nm)a
where a.rnk in(1,2)
union
select*
from 
(select cmt.mch_typ,cutr.mch_nm,count(cutr.trx_amt) trx_amt,dense_rank()over(partition by cmt.mch_typ order by count(cutr.trx_amt)desc) rnk
from cmb_usr_trx_rcd cutr join cmb_mch_typ cmt using(mch_nm)
where usr_id=5201314520
group by cmt.mch_typ,cutr.mch_nm)b
where b.rnk in(1,2)
 
排序。
2024-12-03 时间日期（2）按月统计日花费，一天都不要浪费  
结合讨论区想到两个问题
1、不用max和group by的话使用distinct，也可以确保一个用户在某一个月可能有多笔交易时只取其中一笔吧（代码如下）
2、在对本月天数计算的时候使用substring截取日期字符串最后两位运行结果为错，改用day函数即可通过，是考虑到如‘2024-09-01’，用day函数输出1，substring输出01，虽然两者都是数字形式，但是前者更符合一般逻辑吗？
select distinct substring(cutr.trx_time,1,7) trx_mon,last_day(substring(cutr.trx_time,1,10)) last_day,
substring(last_day(substring(cutr.trx_time,1,10)),-2,2) days_of_mon
from cmb_usr_trx_rcd cutr join cmb_mch_typ cmt using(mch_nm)
where cutr.usr_id=5201314520 and (year(cutr.trx_time) in(2023,2024)) and cmt.mch_typ='休闲娱乐'
order by substring(cutr.trx_time,1,7)
 
1、是的；
2、字符串和数字肯定不相等。
2024-11-18 表连接（5）哪些没被分出来，用左用内你来猜  
任务描述和输出里都没有说明要求输出sum唉，这个坑有点难找出来kkk
 
感谢抓虫，已纠正描述
2024-11-18 表连接（2）渣男去过我对象没去过，那就用LeftJoin  
请问一下这个怎么改
select a.mch_nm asshole_tried,a.trx_cnt, b.mch_nm darling_tried
from
(select mch_nm,count(mch_nm) trx_cnt 
from cmb_usr_trx_rcd
where usr_id='5201314520'and(year(trx_time) in (2023,2024))
group by mch_nm
having count(mch_nm)>=20) a
left join
(select distinct(mch_nm)
from cmb_usr_trx_rcd
where usr_id='5211314521' and year(trx_time) in(2923,2024)) b
using (mch_nm)
order by trx_cnt desc
 
这题很简单啊，典型的左连接。你框架没错，in(2923,2024)) 细节搞错了
2024-11-18 表连接（1）你们难道都去过？那就试试用InnerJoin  
捉个虫，任务要求输出升序，结果正确答案代码order后用的desc
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
 
已改，🌹
2024-11-15 分组与聚合函数（2）擦边营收怎么样，聚合函数可看出  
请问一下下面两种都错在哪里啊
select 
date(trx_time) trx_date, 
max(trx_amt) max_trx_amt,
min(trx_amt) min_trx_amt,
avg(trx_amt) avg_trx_amt, 
sum(trx_amt) total_trx_amt
from cmb_usr_trx_rcd
where usr_id='5201314520' and (date(trx_time) between '2024-09-01' and '2024-09-30') and mch_nm='红玫瑰按摩保健休闲'
group by date(trx_time) 
order by date(trx_time)

select 
date(trx_time) trx_date, 
max(trx_amt) max_trx_amt,
min(trx_amt) min_trx_amt,
avg(trx_amt) avg_trx_amt, 
sum(trx_amt) total_trx_amt
from cmb_usr_trx_rcd
where usr_id='5201314520' and year(trx_time)=2024 and month(trx_time)=9 and mch_nm='红玫瑰按摩保健休闲'
group by date(trx_time) 
order by date(trx_time)
 
是商家的收入 不用限定ID啊 又不止渣男一个客人

提交记录

提交日期 题目名称 提交代码
2024-12-05 销售金额前10的商品信息  
select goods_id,sum(order_gmv) as total_gmv,dense_rank()over(order by sum(order_gmv)desc) ranking
from order_info
where date(order_time)='2024-09-10'
group by goods_id
limit 8
2024-12-05 销售金额前10的商品信息  
select goods_id,sum(order_gmv) as total_gmv,dense_rank()over(order by sum(order_gmv)desc) ranking
from order_info
where date(order_time)='2024-09-10'
group by goods_id
limit 10
2024-12-05 销售金额前10的商品信息  
select a.goods_id as oods_id, a.total_gmv, a.dr as ranking
from
(
select goods_id,sum(order_gmv) as total_gmv,dense_rank()over(order by sum(order_gmv)desc) dr
from order_info
where date(order_time)='2024-09-10'
group by goods_id)a
where a.dr <=10
2024-12-03 窗口函数（2）所有前一和每类前一，继续搞懂排序窗口函数  
select *
from (select null trx_mon,mch_nm,count(trx_amt) trx_amt,dense_rank()over(order by count(trx_amt) desc) rnk
from cmb_usr_trx_rcd
where usr_id=5201314520
group by mch_nm
order by mch_nm desc,rnk)a
where a.rnk in(1,2)
union
select*
from 
(select cmt.mch_typ,cutr.mch_nm,count(cutr.trx_amt) trx_amt,dense_rank()over(partition by cmt.mch_typ order by count(cutr.trx_amt)desc) rnk
from cmb_usr_trx_rcd cutr join cmb_mch_typ cmt using(mch_nm)
where usr_id=5201314520
group by cmt.mch_typ,cutr.mch_nm
order by mch_typ,rnk)b
where b.rnk in(1,2)
2024-12-03 窗口函数（5）越来越喜欢召妓，窗口函数用累计（3）  
select trx_quarter, 
	sum(WithHand) over (order by trx_quarter) withhand,
sum(Doi) over (order by trx_quarter) doi
from 
(select concat(year(trx_time), '-Q', quarter(trx_time)) trx_quarter,
count(trx_amt = 288 or null) WithHand,
count(trx_amt = 888 or null) Doi
from cmb_usr_trx_rcd
where usr_id = 5201314520 and mch_nm = '红玫瑰按摩保健休闲'
group by trx_quarter order by trx_quarter) t
2024-12-03 窗口函数（5）越来越喜欢召妓，窗口函数用累计（3）  
select a.trx_quarter, a.withand, b.doi
from
(select concat(year(trx_time),'-','Q',quarter(trx_time)) trx_quarter,
sum(sum(case when trx_amt=288 then 1 else 0 end)) over(order by concat(year(trx_time),'-','Q',quarter(trx_time)))withand
from cmb_usr_trx_rcd
where usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲'and date(trx_time)>='2023-01-01'
group by concat(year(trx_time),'-','Q',quarter(trx_time))
order by trx_quarter)a
join
(select concat(year(trx_time),'-','Q',quarter(trx_time)) trx_quarter,
sum(sum(case when trx_amt=888 then 1 else 0 end)) over(order by concat(year(trx_time),'-','Q',quarter(trx_time)))doi
from cmb_usr_trx_rcd
where usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲'and date(trx_time)>='2023-01-01'
group by concat(year(trx_time),'-','Q',quarter(trx_time))
order by trx_quarter)b on a.trx_quarter=b.trx_quarter
order by a.trx_quarter
2024-12-03 窗口函数（3）越来越喜欢召妓，窗口函数用累计（1）  
select date_format(cutr.trx_time,'%Y-%m') trx_mon, 
sum(sum(cutr.trx_amt)) over(order by date_format(cutr.trx_time,'%Y-%m') rows between unbounded preceding and current row) trx_amt
from cmb_usr_trx_rcd cutr left join cmb_mch_typ cmt using(mch_nm)
where date(cutr.trx_time)>='2023-01-01' and cutr.usr_id=5201314520
and cmt.mch_typ='休闲娱乐'
group by date_format(cutr.trx_time,'%Y-%m')
order by date_format(cutr.trx_time,'%Y-%m')
2024-12-03 窗口函数（2）所有前一和每类前一，继续搞懂排序窗口函数  
select *
from (select null mch_typ,mch_nm,count(trx_amt) trx_amt,dense_rank()over(order by count(trx_amt) desc) rnk
from cmb_usr_trx_rcd
where usr_id=5201314520
group by mch_nm)a
where a.rnk in(1,2)
union
select*
from 
(select cmt.mch_typ,cutr.mch_nm,count(cutr.trx_amt) trx_amt,dense_rank()over(partition by cmt.mch_typ order by count(cutr.trx_amt)desc) rnk
from cmb_usr_trx_rcd cutr join cmb_mch_typ cmt using(mch_nm)
where usr_id=5201314520
group by cmt.mch_typ,cutr.mch_nm)b
where b.rnk in(1,2)
2024-12-03 窗口函数（2）所有前一和每类前一，继续搞懂排序窗口函数  
select *
from (select null trx_mon,mch_nm,count(trx_amt) trx_amt,dense_rank()over(order by count(trx_amt) desc) rnk
from cmb_usr_trx_rcd
where usr_id=5201314520
group by mch_nm)a
where a.rnk in(1,2)
union
select*
from 
(select cmt.mch_typ,cutr.mch_nm,count(cutr.trx_amt) trx_amt,dense_rank()over(partition by cmt.mch_typ order by count(cutr.trx_amt)desc) rnk
from cmb_usr_trx_rcd cutr join cmb_mch_typ cmt using(mch_nm)
where usr_id=5201314520
group by cmt.mch_typ,cutr.mch_nm)b
where b.rnk in(1,2)
2024-12-03 窗口函数（1）年度前三和每月前三，搞懂排序窗口函数  
select a.trx_mon,a.mch_nm,a.sum_trx_amt
from
(select year(trx_time) trx_mon,mch_nm,sum(trx_amt) sum_trx_amt,row_number()over(order by sum(trx_amt) desc)
from cmb_usr_trx_rcd
where year(trx_time)=2024 and usr_id=5201314520 
group by year(trx_time),mch_nm
limit 3)a
union
select b.trx_mon,b.mch_nm,b.sum_trx_amt
from
(select substring(trx_time,1,7) trx_mon,mch_nm,sum(trx_amt) sum_trx_amt,row_number()over(partition by substring(trx_time,1,7) order by sum(trx_amt) desc) rno
from cmb_usr_trx_rcd
where year(trx_time)=2024 and usr_id=5201314520 
group by substring(trx_time,1,7),mch_nm)b
where b.rno in(1,2,3)
2024-12-03 窗口函数（1）年度前三和每月前三，搞懂排序窗口函数  
select a.trx_mon,a.mch_nm,a.sum_trx_amt
from
(select year(trx_time) trx_mon,mch_nm,sum(trx_amt) sum_trx_amt,dense_rank()over(order by sum(trx_amt) desc)
from cmb_usr_trx_rcd
where usr_id=5201314520 and year(trx_time)=2024
group by year(trx_time),mch_nm
limit 3)a
union
select b.trx_mon,b.mch_nm,b.sum_trx_amt
from
(select substring(trx_time,1,7) trx_mon,mch_nm,sum(trx_amt) sum_trx_amt,dense_rank()over(partition by substring(trx_time,1,7) order by sum(trx_amt) desc) drk
from cmb_usr_trx_rcd
where usr_id=5201314520 and year(trx_time)=2024
group by substring(trx_time,1,7),mch_nm)b
where b.drk in(1,2,3)
2024-12-03 时间日期（2）按月统计日花费，一天都不要浪费  
select distinct substring(cutr.trx_time,1,7) trx_mon,last_day(substring(cutr.trx_time,1,10)) last_day,
day(last_day(substring(cutr.trx_time,1,10))) days_of_mon
from cmb_usr_trx_rcd cutr join cmb_mch_typ cmt using(mch_nm)
where cutr.usr_id=5201314520 and (year(cutr.trx_time) in(2023,2024)) and cmt.mch_typ='休闲娱乐'
order by substring(cutr.trx_time,1,7)
2024-12-03 时间日期（2）按月统计日花费，一天都不要浪费  
select distinct substring(cutr.trx_time,1,7) trx_mon,last_day(substring(cutr.trx_time,1,10)) last_day,
substring(last_day(substring(cutr.trx_time,1,10)),-2,2) days_of_mon
from cmb_usr_trx_rcd cutr join cmb_mch_typ cmt using(mch_nm)
where cutr.usr_id=5201314520 and (year(cutr.trx_time) in(2023,2024)) and cmt.mch_typ='休闲娱乐'
order by substring(cutr.trx_time,1,7)
2024-12-03 时间日期（1）按月统计日花费，一天都不要浪费  
select distinct substring(trx_time,1,7) tex_mon, last_day(substring(trx_time,1,10)) last_day
from cmb_usr_trx_rcd
where usr_id=5201314520 and year(trx_time) in(2023,2024)
order by 1
2024-11-23 被收藏次数最多的商品  
select gd.gd_id, gd.gd_nm, count(xhs.fav_trq) fav_count
from gd_inf gd join xhs_fav_rcd xhs on gd.gd_id=xhs.mch_id
group by xhs.mch_id
order by fav_count desc
limit 1
2024-11-23 德州扑克起手牌-同花  
select sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2 cnt, count(*)/2 ttl_cnt, round((sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2)/(count(*)/2),3) p
from hand_permutations
2024-11-23 德州扑克起手牌- 手对  
select *
from hand_permutations
where left(card1,1)=left(card2,1) and (right(card1,1) != right(card2,2))
order by id
2024-11-23 德州扑克起手牌- A花  
select *
from hand_permutations
where right(card1,1)=right(card2,1) and (card1 like 'A%' or card2 like 'A%')
order by id
2024-11-23 城市平均最高气温  
select city,round(avg(tmp_h),2) avg_tmp_h
from weather_rcd_china
where year(dt)=2021
group by city 
order by avg_tmp_h desc
2024-11-23 不分类别的最火直播间  
select kl1.live_id, kl2.live_nm,count(*) enter_cnt
from ks_live_t1 kl1 join ks_live_t2 kl2 using(live_id)
where date(kl1.enter_time)='2021-09-12' and hour(kl1.enter_time)=23
group by kl1.live_id,kl2.live_nm
order by count(*) desc
limit 5