排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2025-03-12 上月活跃用户数  已解决
2025-03-06 表连接(1)你们难道都去过?那就试试用InnerJoin  已解决

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-03-17 特定渠道的中档单价用户 
select * from apple_pchs_rcd
where order_channel='官网' and payment_method='Apple Pay' and product_price>=3000
order by order_id
2025-03-17 特定渠道的中档单价用户 
select * from apple_pchs_rcd
where payment_method='Apple Pay' and product_price>=3000
order by order_id
2025-03-17 特定渠道的中档单价用户 
select * from apple_pchs_rcd
where payment_method='Apple Pay' and product_price>=3000
2025-03-17 大于J小于K的手牌 
select * from hand_permutations
where card1 > 'J' and card1< 'K'
	 and card2 > 'J' and card2 < 'K'
2025-03-17 语文数学英语至少1门超过100分的同学 
select * from subject_score
where chinese>100 or math>100 or english>100
order by chinese
2025-03-17 性别已知的听歌用户 
select * from qqmusic_user_info
where gender in ('f','m') and year(birth_date)=1980
order by birth_date
2025-03-17 2000年以前出生的男歌手 
select * from singer_info
WHERE year(birth_date)<2000
	and gender='m'
2025-03-17 21世纪上市的银行 
select * from stock_info 
where list_date like '20%'
	and industry='银行'
2025-03-17 输出地区为北京的所有银行 
select * from stock_info
where area='北京' and industry='银行'
order by list_date
2025-03-17 输出地区为北京的所有银行 
select * from stock_info
where area='北京'
order by list_date
2025-03-17 1989年12月13日出生的女歌手 
select * from singer_info
where birth_date='1989-12-13'
2025-03-17 找出所有港台歌手 
select * from singer_info
where type2='港台'
order by singer_id
2025-03-17 找出所有港台乐队 
select *
from singer_info
where type2='港台' and type3='乐队'
order by singer_id
2025-03-17 查询播放量为0的歌手及其专辑 
select
	a.singer_id,
a.singer_name,
c.album_id,
c.album_name,
count(d.song_id) as play_count
from singer_info a
join album_info c using(singer_id)
left join song_info b using(album_id)
left join listen_rcd d using(song_id)
group by 1,2,3,4
having play_count=0
2025-03-17 用户听歌习惯的时间分布 
select
	u.user_id,
dayname(b.start_time) as day_of_week,
count(*) as listen_per_day
from qqmusic_user_info u
join listen_rcd b using(user_id)
group by 1,2
order by u.user_id,2
2025-03-17 特定歌曲的播放记录 
select * from listen_rcd
where date(start_time) between '2023-12-10' and '2023-12-31'
 and song_id=13
order by start_time
2025-03-17 海王发红包 
select
	distinct snd_usr_id
from(
select
	snd_usr_id,
	count(case when pkt_amt in (520,200) then snd_usr_id else null end) as c1
from tx_red_pkt_rcd
group by snd_usr_id
) a
where c1>=5
order by snd_usr_id
2025-03-17 海王发红包 
select
	distinct snd_usr_id
from(
select
	snd_usr_id,
	count(case when pkt_amt = 520 then snd_usr_id else null end) as c1,
	count(case when pkt_amt = 200 then snd_usr_id else null end) as c2
from tx_red_pkt_rcd
group by snd_usr_id
) a
where c1>=5 or c2>=5
order by snd_usr_id
2025-03-17 接收红包金额绿茶榜 
select
	rcv_usr_id,
sum(pkt_amt) as sum_trx_amt
from tx_red_pkt_rcd
where rcv_datetime != '1900-01-01 00:00:00'
group by 1
order by 2 desc
limit 10
2025-03-17 接收红包金额绿茶榜 
select
	snd_usr_id,
sum(pkt_amt) as sum_trx_amt
from tx_red_pkt_rcd
where rcv_datetime != '1900-01-01 00:00:00'
group by snd_usr_id
order by 2 desc
limit 10