排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-29 海王发红包 
select 
	t.*
from
	tx_red_pkt_rcd t
join
	(
		select
			snd_usr_id,
    			sum(case when pkt_amt = '520' then 1 else 0 end) as wel,
    			sum(case when pkt_amt = '200' then 1 else 0 end) as ell
		from tx_red_pkt_rcd
		group by snd_usr_id
    ) a
on 
	t.snd_usr_id = a.snd_usr_id
where 
	wel >= 5 or ell >= 5
order by 
	t.snd_usr_id,
	t.snd_datetime;
想不通,为什么我写的这个不对,求教
是个好问题。发2个520、3个200按题干也算海王,但你的写法找不出来。你把or改成加号试试😏
2024-12-29 S1年级物理成绩前10名(1) 
select 
	s.student_id,name,
	p.phy as score,
	row_number()over(partition by grade_code order by p.phy desc) as rnk
from 
	students s
join 
	(
     	select 
      		student_id,
      		max(if(subject = '物理',score,0)) as phy
    	from 
      		scores
    	group by 
      		student_id
    ) p
on 
	s.student_id = p.student_id    
where 
	grade_code = 'S1'
limit 
	10;
我写的这个为什么不对呀?不明白
单个排序字段无法覆盖逐所有排序主键。输出时把学生id也作为排序字段,就能一一对应啦
2024-12-28 找出所有港台歌手 
题目要求是港台乐队,但是答案却没筛type3
啥也没说

提交记录

提交日期 题目名称 提交代码
2025-02-08 冬季下雪天数 
with tmp as(select city,con
from weather_rcd_china
where month(dt) in (12,1,2) and con like '%雪%')
 select city,count(*) as snowy_days
 from tmp
 group by city
 order by 2 desc;
2024-12-30 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select zn.mch_nm as asshole_tried,trx_cnt,bf.mch_nm as darling_tried
from
(select 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) zn
left join
(select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id = 5211314521 and year(trx_time) in (2023,2024)) bf
onzn.mch_nm = bf.mch_nm
where trx_cnt >= 20
order by 2 desc;
2024-12-30 表连接(1)你们难道都去过?那就试试用InnerJoin 
select zn.mch_nm
from (select distinct mch_nm,usr_id
from cmb_usr_trx_rcd
where usr_id=5201314520 and year(trx_time)=2024) zn
join
(select distinct mch_nm,usr_id
from cmb_usr_trx_rcd
where usr_id=5211314521 and year(trx_time)=2024) bf
on zn.mch_nm = bf.mch_nm
order by 1 desc;
2024-12-30 表连接(1)你们难道都去过?那就试试用InnerJoin 
select zn.mch_nm
from (select distinct mch_nm,usr_id
from cmb_usr_trx_rcd
where usr_id=5201314520 and year(trx_time)=2024) zn
join
(select distinct mch_nm,usr_id
from cmb_usr_trx_rcd
where usr_id=5211314521 and year(trx_time)=2024) bf
on zn.mch_nm = bf.mch_nm
2024-12-30 表连接(1)你们难道都去过?那就试试用InnerJoin 
select 
	distinct c.mch_nm
from 
	cmb_usr_trx_rcd c
join
	(
select 
	mch_nm,usr_id
from 
	cmb_usr_trx_rcd
where 
	usr_id = 5201314520 
and year(trx_time)=2024
) a
on
	c.mch_nm = a.mch_nm
where
	c.usr_id = 5211314521
and year(trx_time)=2024
2024-12-30 表连接(1)你们难道都去过?那就试试用InnerJoin 
select mch_nm from
(select 
	mch_nm,
max(case when usr_id = 5201314520 then 1 else 0 end) as zn,
max(case when usr_id = 5211314521 then 1 else 0 end) as bf
from 
	cmb_usr_trx_rcd
 where 
	year(trx_time) = 2024
group by
	mch_nm
having 
	max(case when usr_id = 5201314520 then 1 else 0 end) = 1 
and max(case when usr_id = 5211314521 then 1 else 0 end) = 1) a
2024-12-30 表连接(1)你们难道都去过?那就试试用InnerJoin 
select mch_nm from
(select 
	mch_nm,
max(case when usr_id = 5201314520 then 1 else 0 end) as zn,
max(case when usr_id = 5211314521 then 1 else 0 end) as bf
from 
	cmb_usr_trx_rcd
group by
	mch_nm
having 
	max(case when usr_id = 5201314520 then 1 else 0 end) = 1 
and max(case when usr_id = 5211314521 then 1 else 0 end) = 1) a
2024-12-30 表连接(1)你们难道都去过?那就试试用InnerJoin 
select 
	mch_nm
from 
	cmb_usr_trx_rcd
group by
	mch_nm
having 
	max(case when usr_id = 5201314520 then 1 else 0 end) = 1 
and max(case when usr_id = 5211314521 then 1 else 0 end) = 1
2024-12-29 查询所有终点是餐饮类地点的行程记录 
select d.*
from didi_sht_rcd d
join loc_nm_ctg l
on end_loc = loc_nm
where loc_ctg = '餐饮'
order by start_tm
2024-12-29 GROUP BY-年龄最大学生的出生日期 
select class_code,min(birth_date) as min_birth_date
from students
group by class_code
order by class_code
2024-12-29 海王发红包 
select 
	t.*
from
	tx_red_pkt_rcd t
join
	(
		select
			snd_usr_id,
			sum(case when pkt_amt = '520' then 1 else 0 end) as wel,
			sum(case when pkt_amt = '200' then 1 else 0 end) as ell
		from tx_red_pkt_rcd
		group by snd_usr_id
) a
on 
	t.snd_usr_id = a.snd_usr_id
where 
	wel >= 5 or ell >= 5
order by 
	t.snd_usr_id,
	t.snd_datetime;
2024-12-29 海王发红包 
select 
	t.*
from
	tx_red_pkt_rcd t
join
	(
		select
			snd_usr_id,
			sum(case when pkt_amt = '520' then 1 else 0 end) as wel,
			sum(case when pkt_amt = '200' then 1 else 0 end) as ell
		from tx_red_pkt_rcd
		group by snd_usr_id
	HAVING 
            COUNT(CASE WHEN pkt_amt IN (520,200) THEN 1 END) >=5
) a
on 
	t.snd_usr_id = a.snd_usr_id
order by 
	t.snd_usr_id,
	t.snd_datetime;
2024-12-29 海王发红包 
select 
	t.*
from
	tx_red_pkt_rcd t
join
	(
		select
			snd_usr_id,
			sum(case when pkt_amt = '520' then 1 else 0 end) as wel,
			sum(case when pkt_amt = '200' then 1 else 0 end) as ell
		from tx_red_pkt_rcd
		group by snd_usr_id) a
on 
	a.snd_usr_id = t.snd_usr_id
where 
	wel >= 5 or ell >= 5
order by 
	t.snd_usr_id,
	t.snd_datetime;
2024-12-29 海王发红包 
select 
	t.snd_usr_id,
	t.rcv_usr_id,
	pkt_amt,snd_datetime,
	rcv_datetime
from
	tx_red_pkt_rcd t
join
	(
		select
			snd_usr_id,
			sum(case when pkt_amt = '520' then 1 else 0 end) as wel,
			sum(case when pkt_amt = '200' then 1 else 0 end) as ell
		from tx_red_pkt_rcd
		group by snd_usr_id) a
on 
	a.snd_usr_id = t.snd_usr_id
where 
	wel >= 5 or ell >= 5
order by 
	t.snd_usr_id,
	t.snd_datetime;
2024-12-29 海王发红包 
select t.snd_usr_id,t.rcv_usr_id,pkt_amt,snd_datetime,rcv_datetime
from
	tx_red_pkt_rcd t
join
(select
	snd_usr_id,
sum(case when pkt_amt = '520' then 1 else 0 end) as wel,
sum(case when pkt_amt = '200' then 1 else 0 end) as ell
from tx_red_pkt_rcd
group by snd_usr_id) a
on a.snd_usr_id = t.snd_usr_id
where wel >= 5 or ell >= 5
order by t.snd_usr_id,snd_datetime
2024-12-29 接收红包金额绿茶榜 
select rcv_usr_id,sum(pkt_amt) as sum_trx_amt
from tx_red_pkt_rcd
where year(rcv_datetime) !=1900
group by rcv_usr_id
order by 2 desc
limit 10;
2024-12-29 接收红包金额绿茶榜 
select rcv_usr_id,sum(pkt_amt) as sum_trx_amt
from tx_red_pkt_rcd
group by rcv_usr_id
order by 2 desc
limit 10;
2024-12-29 找出所有港台歌手 
select *
from singer_info
where type2='港台'
order by singer_id
2024-12-29 条件过滤-符合条件的班主任 
select name,subject,class_code,qualification
from teachers
where fir_degr in ('北京大学','清华大学') and head_teacher is not null
order by name;
2024-12-29 S1年级物理成绩前10名(1) 
select 
	s.student_id,name,
	p.phy as score,
	row_number()over(partition by grade_code order by p.phy desc) as rnk
from 
	students s
join 
	(
 	select 
		student_id,
		max(if(subject = '物理',score,0)) as phy
	from 
		scores
	group by 
		student_id
) p
on 
	s.student_id = p.student_id
where 
	grade_code = 'S1'
limit 
	10;