排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-03-05 至少两门科目大于等于110分的学生  
select 
* 
from subject_score 
where (chinese >=110 and math >=110) or (chinese >=110 and english >=110) or(math >=110 and english >=110)
order by student_id asc;
SELECT student_id, chinese, math, english
FROM (
  SELECT *,
         (chinese >= 110) + (math >= 110) + (english >= 110) AS total
  FROM subject_score
) AS t1
WHERE total >= 2;
SELECT student_id, chinese, math, english
FROM subject_score
HAVING (chinese >= 110) + (math >= 110) + (english >= 110) >= 2;
 
啥也没说
2025-03-04 小结-行转列，展开学生成绩（1）  
这题用SUM也可以输出同样的结果，参考答案用MAX而不是SUM是因为考虑到如果数据存在重复数据需要处理，同个考试可能会有多次成绩的可能性吗
 
啥也没说

提交记录

提交日期 题目名称 提交代码
2026-03-05 时间日期（3）按月统计日花费，一天都不要浪费  
with monthly_base as (
select 
SUBSTR(cu.trx_time, 1, 7) AS trx_mon,
cu.trx_amt
from cmb_usr_trx_rcd cu
join cmb_mch_typ cm on cu.mch_nm = cm.mch_nm
and cm.mch_typ = '休闲娱乐'
where cu.usr_id = 5201314520 
and SUBSTR(cu.trx_time, 1, 4) in (2023,2024)
)
select 
trx_mon,
LAST_DAY(CONCAT(trx_mon, '-01')) AS last_day,
DAY(LAST_DAY(CONCAT(trx_mon, '-01'))) AS days_of_mon,
sum(trx_amt) as trx_amt,
count(1) as trx_cnt,
sum(trx_amt) / DAY(LAST_DAY(CONCAT(trx_mon, '-01'))) as avg_day_amt,
count(1) / DAY(LAST_DAY(CONCAT(trx_mon, '-01'))) as avg_day_cnt
from monthly_base
group by trx_mon
order by trx_mon;
2026-03-04 表连接（5）哪些没被分出来，用左用内你来猜  
select 
	c.mch_typ
,u.mch_nm
,count(1) as trx_cnt
,sum(u.trx_amt) as trx_amt
from cmb_usr_trx_rcd u 
left join cmb_mch_typ c on u.mch_nm = c.mch_nm
where u.usr_id = 5201314520 and year(u.trx_time) = 2024
 	and c.mch_typ is null
group by c.mch_typ,u.mch_nm
order by trx_cnt desc
2026-03-04 表连接（4）渣男把钱花在哪儿，维表可以来帮忙  
select 
	c.mch_typ
,count(1) as trx_cnt
,sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd u 
left join cmb_mch_typ c on u.mch_nm = c.mch_nm
where u.usr_id = 5201314520 and year(u.trx_time) = 2024
group by 1
order by 2 desc
2026-03-04 小结（2）越花越多是死罪，按月统计Substr  
select 
substr(trx_time,1,7) as trx_mon,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt
from
cmb_usr_trx_rcd
where
usr_id = 5201314520 and
date(trx_time) > '2022-11-01' and date(trx_time) <= '2024-12-31'
and 
((truncate(trx_amt,0) rlike "88$|98$" and trx_amt>200) and
hour(trx_time) in (0,23,1,2)
or
upper(mch_nm) rlike "足疗|保健|按摩|养生|SPA")
group by trx_mon
order by trx_mon
2026-03-04 小结（2）越花越多是死罪，按月统计Substr  
SELECT 
	DATE_FORMAT(trx_time, '%Y-%m') AS trx_mon
,count(1) as trx_cnt
,sum(trx_amt) as trx_amt
FROM cmb_usr_trx_rcd 
WHERE 
FLOOR(trx_amt) % 100 IN (88, 98) 
AND trx_amt > 200
AND (TIME(trx_time) >= '23:00:00' OR TIME(trx_time) <= '03:00:00')
AND (
mch_nm LIKE '%足疗%' 
OR mch_nm LIKE '%保健%' 
OR mch_nm LIKE '%按摩%' 
OR mch_nm LIKE '%养生%' 
OR mch_nm LIKE '%SPA%'
)
group by 1
order by 1
2026-02-26 小结（1）大数据早就能扫黄，找足证据不慌张  
select 
	case when floor(trx_amt) % 100 in (88, 98) 
		and trx_amt > 200 
		and (time(trx_time) >= '23:00:00' or time(trx_time) < '04:00:00')
then 'illegal'
else 'other' end as trx_typ
,count(1) as trx_cnt
,sum(trx_amt) as trx_amt
,count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd 
where usr_id = 5201314520 
group by trx_typ
order by trx_cnt desc
2026-02-26 WHERE子查询 + 比较运算符  
select
	*
from cmb_usr_trx_rcd
where trx_amt > (select avg(trx_amt) as avg_amt from cmb_usr_trx_rcd)
order by trx_amt desc
2026-02-26 WHERE子查询 + 比较运算符  
select
	usr_id
,mch_nm
,trx_time
,trx_amt
from cmb_usr_trx_rcd
where trx_amt > (select 
	avg(trx_amt) as avg_amt
from cmb_usr_trx_rcd)
order by trx_amt desc
2026-02-26 基础标量子查询-不带分组  
select 
	usr_id
,mch_nm
,trx_time
,trx_amt
,(select avg(trx_amt) from cmb_usr_trx_rcd) as avg_trx_amt
from cmb_usr_trx_rcd 
where usr_id = 5201314520
order by trx_time desc
2026-02-26 基础标量子查询-不带分组  
select 
	usr_id
,mch_nm
,trx_time
,trx_amt
,(select avg(trx_amt) from cmb_usr_trx_rcd) as avg_trx_amt
from cmb_usr_trx_rcd 
where usr_id = 5201314520 and mch_nm = "红玫瑰按摩保健休闲"
order by trx_time desc
2026-02-26 基础标量子查询-带分组  
select distinct
usr_id,
sum(trx_amt) over (partition by usr_id) as total_amt,
avg(trx_amt) over () as platform_avg_amt 
from cmb_usr_trx_rcd
order by total_amt desc
2026-02-26 基础标量子查询-带分组  
SELECT 
    usr_id,
    SUM(trx_amt) AS total_amt,
    (SELECT AVG(trx_amt) FROM cmb_usr_trx_rcd) AS platform_avg_amt
FROM cmb_usr_trx_rcd
GROUP BY usr_id
ORDER BY total_amt DESC;
2026-02-26 基础标量子查询-带分组  
with t1 as(
select
	avg(trx_amt) as platform_avg_amt
from cmb_usr_trx_rcd)
select 
	usr_id
,sum(trx_amt) as total_amt
	,platform_avg_amt
from cmb_usr_trx_rcd 
cross join t1
group by usr_id, t1.platform_avg_amt
order by total_amt desc
2026-02-26 基础标量子查询-带分组  
with t1 as(
select
	avg(trx_amt) as platform_avg_amt
from cmb_usr_trx_rcd)
select 
	usr_id
,sum(trx_amt) as total_amt
	,platform_avg_amt
from cmb_usr_trx_rcd 
cross join t1
group by usr_id, t1.platform_avg_amt
order by total_amt desc
limit 5
2026-02-26 基础标量子查询-带分组  
with t1 as(
select
	avg(trx_amt) as avg_trx_amt
from cmb_usr_trx_rcd)
select 
	mch_nm
,sum(trx_amt) as total_trx_amt
	,avg_trx_amt
from cmb_usr_trx_rcd 
cross join t1
group by mch_nm, t1.avg_trx_amt
2025-03-07 A和K之间的手牌（3）  
select * from hand_permutations 
where card1 between 'A' and 'K' and card2 between 'A' and 'K'
2025-03-07 A和K之间的手牌（2）  
select * from hand_permutations
where card1 between 'A' and 'K' or card2 between 'A' and 'K'
2025-03-07 A和K之间的手牌（1）  
select 
	* 
from hand_permutations 
where card1 between 'A' and 'K'
order by id
2025-03-07 交易金额在5000至10000（含边界）的所有交易  
select * from cmb_usr_trx_rcd 
where trx_amt between 5000 and 10000
order by trx_amt desc
limit 5
2025-03-07 查询播放量为0的歌手及其专辑  
SELECT 
    s.singer_id,
    s.singer_name,
    a.album_id,
    a.album_name,
    COUNT(l.id) AS play_count
FROM 
    singer_info s
JOIN 
    album_info a ON s.singer_id = a.singer_id
LEFT JOIN 
    song_info sg ON a.album_id = sg.album_id
LEFT JOIN 
    listen_rcd l ON sg.song_id = l.song_id
GROUP BY 
    s.singer_id, s.singer_name, a.album_id, a.album_name
HAVING 
    play_count = 0;