排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-07-26 数学成绩分段统计（3）  
with df as (
  select s.class_code,
		s.student_id,
		case 
        	when score>110 then 'excellent'
            when score>=90 then 'good'
            when score>=60 then 'well'
            when score<60 then 'fail'
            end as def
from students s
left join scores sc on s.student_id=sc.student_id
where exam_date='2024-06-30'and subject='数学')

select class_code,ttl,
concat(excellent,',',exrate)as exc,
concat(good,',',goodrate)as exc,
concat(well,',',wellrate)as exc,
concat(fail,',',failrate)as exc
from
(select class_code,
		count(distinct case when def='excellent'then student_id else null end)as excellent,
        count(distinct case when def='good'then student_id else null end)as good,
        count(distinct case when def='well'then student_id else null end)as well,
        count(distinct case when def='fail'then student_id else null end)as fail,
        count(distinct student_id)as ttl,
        concat(round(count(distinct case when def='excellent'then student_id else null end)/count(distinct student_id)*100,2),'%') as exrate,
        concat(round(count(distinct case when def='good'then student_id else null end)/count(distinct student_id)*100,2),'%' )as goodrate,
        concat(round(count(distinct case when def='well'then student_id else null end)/count(distinct student_id)*100,2),'%') as wellrate,
        concat(round(count(distinct case when def='fail'then student_id else null end)/count(distinct student_id)*100,2),'%') as failrate
from df
group by class_code)t1 

为什么显示错误
 
去掉distinct试试呢？
sum case when时是对每一行求和（人次）；你的count distinct 是对去重后学生数求和。
2025-07-26 平均分最高的班级  
WITH total_score AS (
	SELECT 
        s.class_code,
        COUNT(DISTINCT s.student_id) AS cnt,
        SUM(sc.score) AS sum,
        t.name
	FROM students s
	INNER JOIN scores sc ON s.student_id = sc.student_id
	INNER JOIN teachers t ON s.class_code = t.head_teacher
	GROUP BY s.class_code, t.name
)  为什么输出不了结果呀
 
WITH total_score AS (
	SELECT 
        s.class_code,
        COUNT(DISTINCT s.student_id),
        SUM(sc.score) AS sum,
        t.name
	FROM students s
	INNER JOIN scores sc ON s.student_id = sc.student_id
	INNER JOIN teachers t ON s.class_code = t.head_teacher
	GROUP BY s.class_code, t.name
) 
select * from total_score 。你with创建了一个子查询，后面没有select动作了，当然会报错了

提交记录

提交日期 题目名称 提交代码
2025-08-01 时间日期（2）按月统计日花费，一天都不要浪费  
select substr(trx_time,1,7) as trx_mon ,last_day(max(trx_time)) as last_day, day(last_day(max(trx_time)) ) as day_of_mon
from cmb_usr_trx_rcd a
left join cmb_mch_typ m
on a.mch_nm = m.mch_nm
where a.usr_id=5201314520 and year(a.trx_time) in (2023, 2024) and m.mch_typ='休闲娱乐'
group by substr(trx_time,1,7)
order by 1
2025-08-01 时间日期（2）按月统计日花费，一天都不要浪费  
select month(trx_time) as trx_mon ,last_day(max(trx_time)) as last_day, day(last_day(max(trx_time)) ) as day_of_mon
from cmb_usr_trx_rcd a
left join cmb_mch_typ m
on a.mch_nm = m.mch_nm
where a.usr_id=5201314520 and year(a.trx_time) in (2023, 2024) and m.mch_typ='休闲娱乐'
group by month(trx_time)
order by 1
2025-08-01 表连接（5）哪些没被分出来，用左用内你来猜  
SELECT 
    m.mch_typ, u.mch_nm,
    COUNT(u.trx_amt) AS trx_cnt, 
    SUM(u.trx_amt) AS trx_amt
FROM 
    cmb_usr_trx_rcd u
LEFT JOIN 
    cmb_mch_typ m ON u.mch_nm = m.mch_nm
WHERE 
    u.usr_id = '5201314520' and year(trx_time)=2024 and m.mch_typ is null
GROUP BY 
    m.mch_typ,u.mch_nm
ORDER BY
    3 DESC
2025-08-01 表连接（5）哪些没被分出来，用左用内你来猜  
select mch_typ,c.mch_nm,count(trx_time)cnt,sum(trx_amt)amount
from cmb_usr_trx_rcd c
left join cmb_mch_typ t on c.mch_nm=t.mch_nm
where mch_typ is null
group by mch_typ,mch_nm
order by cnt desc
2025-08-01 表连接（5）哪些没被分出来，用左用内你来猜  
select mch_typ,c.mch_nm,count(trx_time)cnt,sum(trx_amt)amount
from cmb_usr_trx_rcd c
left join cmb_mch_typ t on c.mch_nm=t.mch_nm
where mch_typ is null
group by mch_typ,mch_nm
2025-07-30 表连接（4）渣男把钱花在哪儿，维表可以来帮忙  
select mch_typ,count(*)cnt,sum(trx_amt)money
from cmb_usr_trx_rcd c
left join cmb_mch_typ mc on c.mch_nm=mc.mch_nm
where c.usr_id = 5201314520 and year(trx_time)=2024
group by mch_typ
order by money desc
2025-07-30 表连接（4）渣男把钱花在哪儿，维表可以来帮忙  
select mch_typ,count(*)cnt,sum(trx_amt)money
from cmb_usr_trx_rcd c
left join cmb_mch_typ mc on c.mch_nm=mc.mch_nm
where c.usr_id = 5201314520
group by mch_typ
order by money desc
2025-07-30 表连接（4）渣男把钱花在哪儿，维表可以来帮忙  
select mch_typ,count(*)cnt,sum(trx_amt)
from cmb_usr_trx_rcd c
left join cmb_mch_typ mc on c.mch_nm=mc.mch_nm
where c.usr_id = 5201314520
group by mch_typ
2025-07-29 从未被领取的优惠券  
SELECT DISTINCT c.*
FROM coupons c
LEFT JOIN user_coupons uc ON c.coupon_id = uc.coupon_id
WHERE uc.user_id IS NULL
ORDER BY c.coupon_id;
2025-07-29 从未被领取的优惠券  
select coupon_id
from coupons
where coupon_id not in 
(select coupon_id
from order_promotions o
where coupon_id is not null)
2025-07-29 使用了全场通用优惠券的订单数量  
select count(*)cnt
from order_promotions o
inner join coupons c on o.coupon_id=c.coupon_id
group by coupon_type
having coupon_type='全场通用'
2025-07-29 使用了全场通用优惠券的订单数量  
select coupon_type,count(*)cnt
from order_promotions o
inner join coupons c on o.coupon_id=c.coupon_id
group by coupon_type
having coupon_type='全场通用'
2025-07-29 查询播放量为0的歌手及其专辑  
select *
from 
(select s.singer_id,singer_name,ai.album_id,album_name,count(lr.start_time)cnt
from singer_info s
left join song_info si on s.singer_id = si.origin_singer_id
left join listen_rcd lr on si.song_id = lr.song_id
left join album_info ai on s.singer_id=ai.singer_id
group by s.singer_id,singer_name,album_id,album_name
order by cnt asc)t1
where cnt = 0
2025-07-29 查询播放量为0的歌手及其专辑  
select *
from 
(select s.singer_id,singer_name,ai.album_id,album_name,count(lr.start_time)cnt
from singer_info s
left join song_info si on s.singer_id = si.origin_singer_id
left join listen_rcd lr on si.song_id = lr.song_id
left join album_info ai on si.album_id = ai.album_id
group by s.singer_id,singer_name,album_id,album_name
order by cnt asc)t1
where cnt = 0
2025-07-29 查询播放量为0的歌手及其专辑  
select *
from 
(select s.singer_id,singer_name,si.album_id,album_name,count(lr.start_time)cnt
from singer_info s
left join song_info si on s.singer_id = si.origin_singer_id
left join listen_rcd lr on si.song_id = lr.song_id
left join album_info ai on si.album_id = ai.album_id
group by s.singer_id,singer_name,album_id,album_name
order by cnt asc)t1
where cnt = 0
2025-07-28 拼接歌曲名和专辑名  
select song_name,concat(song_name,'-',album_name)
from song_info s
inner join album_info a on s.album_id=a.album_id
2025-07-28 拼接歌曲名和专辑名  
select concat(song_name,'-',album_name)
from song_info s
inner join album_info a on s.album_id=a.album_id
2025-07-28 拼接歌曲名和专辑名  
select concat(album_name,'-',song_name)
from song_info s
inner join album_info a on s.album_id=a.album_id
2025-07-28 拼接歌曲名和专辑名  
select song_name,album_name,concat(album_name,'-',song_name)
from song_info s
inner join album_info a on s.album_id=a.album_id
2025-07-28 用户听歌习惯的时间分布  
select u.user_id,
dayname(lr.start_time) as wk,
count(*)cnt
from qqmusic_user_info u
left join listen_rcd lr on u.user_id=lr.user_id
group by 1,2
order by user_id asc,wk asc